Forum Discussion
Multiple IF Conditions in Custom Column
Ah, I did some playing around with it, and found out that you can only do one comparison for a range and still get a Boolean result. So you can do Projecten[mMergeFactor]<=4 but not 3<Projecten[mMergeFactor]<=4. We could make this messy setting up a bunch of AND statements, similar to your original answer. But there's a cleaner and clearer way!
SWITCH statements will use whichever condition evaluates to TRUE first, so we can go back and set up the conditions with that in mind:
mMargecijfer = SWITCH(true(); 5<=Projecten[mMargeFactor];1; 4<=Projecten[mMargeFactor];0,9; 3<=Projecten[mMargeFactor];0,8; 2<=Projecten[mMargeFactor];0,7; 1<=Projecten[mMargeFactor];0,6; 0<=Projecten[mMargeFactor];0,5; -1<=Projecten[mMargeFactor];0,4; -2<=Projecten[mMargeFactor];0,3; -3<=Projecten[mMargeFactor];0,2; 0,1)
So say the mMargeFactor has a value of 3.87. The way this works is that the SWITCH is looking for the first value that is the same as the Expression, in this case TRUE(). The query tests if 5<=3.87. The answer is no, so it moves on. It checks if 4<=3.87, and once again the result is false, so it moves on. Then it checks if 3<=3.87 and the result is true, so it fills in 0.8 as your value. It does NOT check the other conditions, so make sure your highest priority conditions come first.
At the bottom, we use the ELSE condition instead of another test, since we don't know how low the value is, but we know it's less than -3 at this point.
Hope this helps YvesL
Instead of using a column, create a new Measure for this!
It's possible to do with multiple IF statements, but would be easier with a SWITCH function. You've got a slightly harder example, since SWITCH usually only is able to compare exact values, but you can trick it into handling ranges as described here. Try something like this:
KOLOM =
SWITCH(
TRUE(),
Projecten[mMargeFactor]>5, 1,
4<Projecten[mMargeFactor]<5, .9,
3<Projecten[mMargeFactor]<4, .8,
2<Projecten[mMargeFactor]<3, .7,
1<Projecten[mMargeFactor]<2, .6,
0<Projecten[mMargeFactor]<1, .5,
-1<Projecten[mMargeFactor]<0, .4,
-2<Projecten[mMargeFactor]<-1, .3,
-3<Projecten[mMargeFactor]<-2, .2,
Projecten[mMargeFactor]<-3, .1
)
You'll likely need to update this depending on how you want to handle boundary cases (if Projecten[mMargeFactor] is exactly equal to 5, for example) but it should get you started.