Switching Calculation Filter Dynamically

WZorn — Tue, 13 Sep 2022 14:57:50 GMT

I want to create a measure that tests if a set of data is empty when filtered. If it is, then use a different filter to get another set of data. Not sure the best way to describe it other than the by example below.

When sliced by Category, if data for M1 exists, I want to graph M1 values, if it doesn't, I want to graph M2 values.

My measure doesn't quite give me what I want.

DATA:

Category	Metric	Year	Value
Category 1	M1	2020	3
Category 1	M1	2021	8
Category 1	M1	2022	3
Category 1	M2	2020-FY	4
Category 1	M2	2021-FY	9
Category 1	M2	2022-FY	2
Category 2	M1	2020	2
Category 2	M1	2021	1
Category 2	M1	2022	7
Category 3	M2	2020-FY	2
Category 3	M2	2021-FY	6
Category 3	M2	2022-FY	4

A SLICER is setup for Catagory.

A LINE CHART is setup with Axis = "Year" and Values = "Measure"

Where

Measure = IF(

'Table'[M1 EMPTY],

CALCULATE(

SUM('Table'[Value]),

FILTER('Table', 'Table'[Metric] = "M2")

CALCULATE(

SUM('Table'[Value]),

FILTER('Table', 'Table'[Metric] = "M1")

)

and

M1 EMPTY = ISEMPTY(

CALCULATETABLE(

'Table',

'Table'[Metric] = "M1"

)

MY RESULTS:

If I select "Category 2" in the slicer, I get what I expect only M1 Values:

Selecting Category 3, shows only M2 values:

BUT, when I select "Category 1", it's not giving what I expect. I WANT M1 values only, but I get BOTH M1 and M2 values.

Re: Switching Calculation Filter Dynamically

Anonymous — Wed, 14 Sep 2022 02:41:15 GMT

Hi WZorn ,

Please try below dax formula:

Measure = VAR cur_category = SELECTEDVALUE ( 'Table'[Category] ) VAR tmp_m1 = CALCULATETABLE ( 'Table', 'Table'[Metric] = "M1", ALLSELECTED ( 'Table'[Category] ) ) VAR sum_m1 = SUMX ( tmp_m1, [Value] ) VAR tmp_m2 = FILTER ( 'Table', 'Table'[Metric] = "M2" ) VAR sum_m2 = SUMX ( tmp_m2, [Value] ) VAR tmp_m3 = FILTER ( 'Table', 'Table'[Metric] = "M3" ) VAR sum_m3 = SUMX ( tmp_m3, [Value] ) RETURN SWITCH ( cur_category, "Category 1", sum_m1, "Category 2", sum_m1, "Category 3", sum_m2 )

Select "Category 1":

Select "Category 2":

Select "Category 3":

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: Switching Calculation Filter Dynamically in DAX Commands and Tips

Switching Calculation Filter Dynamically

Re: Switching Calculation Filter Dynamically