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

[natg]

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.

[v-ljerr-msft]

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. 

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]

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]

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. 

Regards