5 Replies Latest reply on May 12, 2017 9:16 AM by Joe Oppelt

    Calc to display another measure if first measure = 'All'?

    mike.shapiro.0

      I'm using a <MSA> measure in a title to display the MSA name.  Is there a way when all measures are selected to have it use another measure?

       

      I tried IF [MSA] = 'All' THEN [STATE] ELSE [MSA] END

       

      That did not work.

        • 1. Re: Calc to display another measure if first measure = 'All'?
          Galen Busch

          Hi Mike,

           

          As you've found, you can't reference a filter value. Essentially, you're just comparing rows of data (values contained in [MSA] with the word all.

           

          Try doing:

           

          IF {FIXED: COUNTD([MSA]) } >1 then [STATE]

           

           

          ELSA [MSA] END

           

          Be sure to put the filter that is effecting this on context.

           

          Galen

          • 2. Re: Calc to display another measure if first measure = 'All'?
            Joe Oppelt

            "All" is just a convenience Tableau adds to your filters.  It's not a data value to examine.

             

            What you want to do is create a calc that tells you the number of possible values in your database and compare that to the number on the sheet.

             

            { FIXED : COUNTD([MSA]) }

             

            That will tell you what's in your database.

             

            Depending on how your sheet is set up, COUNTD([MSA]) or some LOD calc that takes into account the dimensions on your sheet will tell you how many [MSA]s are actually selected on your sheet.

             

            Then your final calc would be like this:

             

            IF [MSA in the database CALC] = [MSA on the sheet CALC] THEN [STATE] ELSE [MSA] END

            • 3. Re: Calc to display another measure if first measure = 'All'?
              Joe Oppelt

              Addendum to my reply...

               

              Right now if you just add <MSA> to your title, and if multiple MSAs are selected, your title will say things like:

               

              "MSA1, MSA2, and 3 others" ...  (If you selected 5 MSAs, for example.)

               

              But if you put the calc I suggested, you will not get that Tableau-generated string any more.  You'll get an asterisk.  Tableau generates that special string for you because the variable you used is the same as the filter for the sheet.

               

              Well, let me qualify that.  I'm pretty sure that's what is going to happen.

               

              Do you still want that special string if 2 or more (but not ALL) are selected?  Try out what I suggested first and see if you are OK with the behavior it generates.  If you get the asterisk, and if you want that string, there is an example out there (moderately complicated) that lets you create that string for yourself.  But I don't want to send you down a rabbit hole you don't need.

              • 4. Re: Calc to display another measure if first measure = 'All'?
                mike.shapiro.0

                Hi Joe,

                 

                I tried that code, it is automatically moving to STATE regardless of the number of MSA's selected. 

                 

                What I'd prefer, for visual sake. 

                 

                If one MSA is selected, using MSA Filter, then show the MSA name.  If multiple, but not all selected, show 'Multiple MSA's.  If 'All' selected, using MSA 'All' filter option, then show [State]

                 

                Here is a view of the header

                 

                header example.png

                • 5. Re: Calc to display another measure if first measure = 'All'?
                  Joe Oppelt

                  Post a sample workbook.

                   

                  I'll show you.