3 Replies Latest reply on May 1, 2018 7:39 AM by swaroop.gantela

    Filtering based on measure selected

    Ryan Soares

      I have the attached sample data with a list of names and 4 numeric measures. I have created the simple dashboard below. The pie chart shows the averages of P1-P4 over all the listed names.

       

      I want to set up a dashboard action so that when a section of the pie chart is clicked, the table on the right will filter out the names with 0 for whichever section. So if P1 is clicked, E would be filtered out since it is 0 for P1. If P4 is clicked, B,C,D, F and G would be filtered out. How can I achieve this?

        • 1. Re: Filtering based on measure selected
          swaroop.gantela

          Ryan,

           

          I made an attempt in the workbook attached in the Forum thread,

          but it required me to pivot the columns so that all the measures were in one column.

          This allowed for use of a Level of Detail calculation that took into account the measure name.

           

          Checking for non-zero:

          IF SUM([Pivot Field Values])>0 THEN 1 ELSE 0 END

           

          Then fixing that check to the Measure and the name:

          { FIXED [Pivot Field Names],[Name]:([Check nonzero])}

           

          That was placed on the detail shelf for the Pie as a Dimension.

          The dashboard action then was run on the fields Pivot Field Names

          and FixNonZero.

           

          There are likely other better ways.

          1 of 1 people found this helpful
          • 2. Re: Filtering based on measure selected
            Ryan Soares

            Hi

             

            Thanks for your help. This is good, but I'd like the numbers for P1-P4 to still show when a section of the pie is clicked. Right now it only shows one column which is the section that was clicked. I'd like to still show all 4 and just remove the rows that have a 0 value for whichever of P1-P4 is selected.

            Thanks.

            • 3. Re: Filtering based on measure selected
              swaroop.gantela

              Ryan,

               

              This next part took quite a number of steps.

              There is likely a much easier way.

               

              This may not at all be feasible for you because:

              -I first needed to pivot all the P1,P2,etc into one column

              -I then needed to join this pivot to itself.

               

              Would your true datasource allow for such rearrangements?

              Would you be able to achieve this through Tableau Prep?

               

              If these are feasible for you, then I will proceed.

               

              I think the self-join is necessary because after filtering on P1, say,

              then all the other P2,P3,P4 values need to still be accessible.

               

              Because of the self-join, the "all or one" flag is needed to tell

              if a pie slice has been selected or not:

              IF WINDOW_MAX(MAX([Metric]))<>WINDOW_MIN(MIN([Metric]))

              THEN "All"  ELSE "One" END

               

              If no slice selected, just show one set of values:

              IF [All or One]="All" AND ATTR([Metric])="P1" THEN TRUE

              ELSEIF [All or One]="One" THEN TRUE END

               

              Then if a slice has been selected, check if selected has 0 value:

              IF [All or One]="One"

              AND ATTR([Metric])=ATTR([Metric (Sheet1-Tableau1)])

              AND SUM([Value (Sheet1-Tableau1)])=0

              THEN 0 ELSE 1 END

               

              Filter those rows that have a 0 for the selected metric:

              IF WINDOW_MIN([Zero for Metric])=0

              THEN 0 ELSE 1 END

               

              Please see workbook attached in Forum thread.