Forum Discussion
Helper IIF one value then...
Hi, All
I've spent the day trying to figure out how to do this - I thought I was getting somewhere using SWITCH(TRUE() statement but can't seem to get it to work.
The situation is I have a measure which works out the percentage of activity, if the client hit's say 6.6% they will be paid 200 and so on. I even thought about building a table as such below but then couldn't figure out how to reference it (if it would even work?). I'm sure it can work - I just can't figure it out. If anyone can help it would be most appreciated. Thanks.
| 6.6 | 200 |
| 6.888372 | 400 |
| 7.176744 | 600 |
| 7.465116 | 800 |
| 7.753488 | 1000 |
| 8.04186 | 1200 |
| 8.330233 | 1400 |
| 8.618605 | 1600 |
| 8.906977 | 1800 |
| 9.195349 | 2000 |
Parosel , Try a measure like
Sumx(Values(Table[Client]) , Calculate(Switch( True() ,
[Percent] >= 9.195349, 2000,
[Percent] >= 8.906977,1800,
//Add others
[Percent] >= 6.6 , 200
) ) )
@Parosel please consider accepting @amitchandak solution which is tecvhnically better than yours.
@amitchandak shas wisely put the highest value first, because the switch command will trigger when it recahes the first >= true condition.
Whereas @Parosel has tested > and <.
This is two times slower and will not be True when the value is =
Well done @amitchandak !
3 Replies
- amitchandak
Super User
Parosel , Try a measure like
Sumx(Values(Table[Client]) , Calculate(Switch( True() ,
[Percent] >= 9.195349, 2000,
[Percent] >= 8.906977,1800,
//Add others
[Percent] >= 6.6 , 200
) ) )
Paroselamitchandak thanks for this I applied something similar
Estimated payment at current A&G Rate = SWITCH(TRUE (),AA_Measures[A&G to OP rate] > 0.066 && AA_Measures[A&G to OP rate] < 0.0688372, "£200",AA_Measures[A&G to OP rate] > 0.0688372 && AA_Measures[A&G to OP rate] < 0.07176744186, "£400",AA_Measures[A&G to OP rate] > 0.07176744186 && AA_Measures[A&G to OP rate] < 0.07465116279, "£600",AA_Measures[A&G to OP rate] > 0.07465116279 && AA_Measures[A&G to OP rate] < 0.07753488372, "800",
speedramps@Parosel please consider accepting @amitchandak solution which is tecvhnically better than yours.
@amitchandak shas wisely put the highest value first, because the switch command will trigger when it recahes the first >= true condition.
Whereas @Parosel has tested > and <.
This is two times slower and will not be True when the value is =
Well done @amitchandak !