IF one value then...

[Parosel]

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

[amitchandak · Accepted solution]

@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

) ) )

[amitchandak · Accepted solution]

@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]

@amitchandak 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 · Accepted solution]

@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  !