Switching Calculation Filter Dynamically

[WZorn]

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.

 

[v-binbinyu-msft · Accepted solution]

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.