Measure Using SWITCH Not Applying Filters to Underlying Measures

[taylorb]

I am trying to minimize the number of measures I am using in a dashboard. I ended up creating several that are nearly identical so I thought I could use some IF statements to reduce the amount of measures. The IF statement works great, but I am also using a SWITCH in a calculated column that calls the IF measures and when using the SWITCH, it does not apply the filter to the IF in the original measure.

IF Measure:

!ExternalAvgRates = 
var bill = Round(CALCULATE(AVERAGE(Job[BILL_RATE]), Job[Type] <> "Internal")/DISTINCTCOUNTNOBLANK(Calendar[CALENDAR_DATE]),2)
var pay = Round(CALCULATE(AVERAGE(Job[PAY_RATE]), Job[Type] <> "Internal")/DISTINCTCOUNTNOBLANK(Calendar[CALENDAR_DATE]),2)
var rateType = SELECTEDVALUE('Rate Type'[Rate Type])
return
if(rateType = "Pay", pay, bill)

I have a page level filter:

So far, it all works. Next I try this switch statement (in a calculated column):

Avg Rate = 
SWITCH('Summary Cards'[Line of Business],
    "External", [!ExternalAvgRates],
    "Internal", [!InteralAvgRates],
    "Interdepartmental", [!InterdeptAvgRates],
    "New Hire", [!NewHireAvgRates],
    "Tenured", [!TenuredAvgRates],
    )

This switch will display the Bill rate types despite the page filter showing only Pay rate, and the original measure on the same page displays correctly.

What am I doing wrong here?

[V-pazhen-msft]

@taylorb 

You are right, Power bi not support create a dynamic calculated column based on slicer/filter, the calculated column has the "row context" of the current row. It is calculated once when the data set is loaded, and the stored value will not change according to the user's selection, that is, it is not affected by, for example, the slicer.

Measures are dynamic, they are calculated when necessary, so they will respond to the slicer in the report.

This is the difference between calculated column and measure:

https://radacad.com/measure-vs-calculated-column-the-mysterious-question-not

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@taylorb , if it is a measure then it should be like this assuming all other are measures too

Avg Rate = 
SWITCH(max('Summary Cards'[Line of Business]),
    "External", [!ExternalAvgRates],
    "Internal", [!InteralAvgRates],
    "Interdepartmental", [!InterdeptAvgRates],
    "New Hire", [!NewHireAvgRates],
    "Tenured", [!TenuredAvgRates],
    )

[taylorb]

Sorry, I made a mistake in my original post (I have corrected it). The SWITCH is in a calculated column not a measure. Sorry for the confusion

[taylorb]

I think I answered my own question... obviously a calculated column wont take a filter into its calculation.