Forum Discussion

natg's avatar
natg
Frequent Visitor
8 years ago

Ability to dynamically filter data based on conditions that is not mutually exclusive

Hi,

I have a fact table which contais a category variable with possible values as below 

 

- Basic

- Inter

- Advanced

- Misc


User wants a filter to say wether to Include OR Exclude the "Misc" category in the report.

The visual should be through a filter with two options Include and Exclude (Through standard slicer or through chiclet slicer).

 

Easier option is to include all the 4 values and ask the user to select all OR exclude MISC option only. But looking for a simple interface where user can do this with one click option.

Need Help
Show and Tell

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi natg,

     

    If I understand you correctly, you can firstly add a filter with two options Include and Exclude on your report, then try using the formula below to create a measure to calculate values to Include OR Exclude the "Misc" category. :smileyhappy:

    Measure =
IF (
    HASONEVALUE ( Table1[Option] ),
    IF (
        VALUES ( Table1[Option] ) = "Exclude",
        CALCULATE (
            SUM ( Table2[Value] ),
            FILTER ( ALL ( Table2 ), Table2[Category ] <> "Misc" )
        ),
        SUM ( Table2[Value] )
    ),
    SUM ( Table2[Value] )
)

     

    Regards

    • natg's avatar
      natg
      Frequent Visitor
      8 years ago

      Thnaks for the solution workaround v-ljerr-msft

      Will try this and revert back.

       

      Since this filter is kind of a global filter to be applied to 20+ metrices shown in the dashboard, thinking of implementing through joins with additional views in the model so that the existing calculations remain that way. 

       

      Is my understaning correct that I need to use the condional logic in all the measures that are impacted by the filter on category column.

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi natg,

        Is my understaning correct that I need to use the condional logic in all the measures that are impacted by the filter on category column. 

        Yes, that's correct.

        Since this filter is kind of a global filter to be applied to 20+ metrices shown in the dashboard, thinking of implementing through joins with additional views in the model so that the existing calculations remain that way. 

        It seems to be a better solution. :smileyhappy:

         

        Regards