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 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:

CategoryMetricYearValue
Category 1M12020   3
Category 1M12021   8
Category 1M12022   3
Category 1M22020-FY   4
Category 1M22021-FY   9
Category 1M22022-FY   2
Category 2M12020   2
Category 2M12021   1
Category 2M12022   7
Category 3M22020-FY   2
Category 3M22021-FY   6
Category 3M22022-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.

 

 

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.

1 Reply

  • 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.