Forum Discussion

WZorn's avatar
WZorn
Helper III
3 years ago
Solved

Switching Calculation Filter Dynamically

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 ...
DAX FILTER
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.