Forum Discussion

am_i_really's avatar
am_i_really
Frequent Visitor
3 years ago

Switch statement based on another measure

Hi All, 

 

I'm trying to write a measure that will give me a specific value based on the result of another measure. I have the following measure "NON-PM Score = DIVIDE([NON-PM On Time], [NON-PM Total])" that gives me a percentage. Based on this percentage I am trying to write a measure that will give me a number 1-5. This is what I was trying to use but it doesnt not work:

Measure = SWITCH(
    TRUE(),
    'Measures Table'[NON-PM Score] > .95, "5",
    'Measures Table'[NON-PM Score] > .89 < .95, "4",
    'Measures Table'[NON-PM Score] > .79 < .90, "3",
    'Measures Table'[NON-PM Score] > .69 < .80, "2",
    'Measures Table'[NON-PM Score] < .70 , "1"
)

What I basically need is for the measure for show a
5: 95% and higher
4: 90 - 94.9%

3: 80 - 89.9%

2: 70 - 79.9%
1: anything less than 70%


Thank you in advance!

"DAX"
@Power BI

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    am_i_really Hard to say why it isn't working. You could simplify it and get better performance this way. Perhaps provide sample data and a description of what is going wrong.

    Measure = 
  VAR __Measure = 'Measures Table'[NON-PM Score]
  SWITCH(
    TRUE(),
    __Measure < .7, "1",
    __Measure > .69 < .80, "2",
    __Measure > .79 < .90, "3",
    __Measure > .89 < .95, "4",
    "5"
  )