Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
natg
Frequent Visitor

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.

3 REPLIES 3
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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

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.

 

 

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. Smiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.