Forum Discussion

taylorb's avatar
taylorb
Helper I
5 years ago

Measure Using SWITCH Not Applying Filters to Underlying Measures

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?

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      taylorb
      Helper I
      5 years ago

      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's avatar
        taylorb
        Helper I
        5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.