Multiple IF Conditions in Custom Column

[YvesL]

hi,

im new quite new to power bi and i'm struggling with a multiple If function

i need this

if column A is bigger then 5 = result 1

if column A is between 4 and 5 = result 0.9

if column A is between 3 and 4 = result 0.8

if column A is between 2 and 3 = result 0.7

if column A is between 1 and 2 = result 0.6

if column A is between 0 and 1 = result 0.5

if column A is between -1 and 0 = result 0.4

if column A is between -2 and -1 = result 0.3

if column A is between -3 and -2 = result 0.2

if column A is lower then -3 = result 0.1

i made this :

Kolom =
IF((Projecten[mMargeFactor])>=0 && (projecten[mMargeFactor])<1 ; 0,5 ; 1 )
IF((Projecten[mMargeFactor])>=1 && (projecten[mMargeFactor])<2 ; 0,6 ; 1 )
IF((Projecten[mMargeFactor))>=2 && (projecten[mMargeFactor])<3 ; 0,7 ; 1 )
IF((Projecten[mMargeFactor])>=3 && (projecten[mMargeFactor])<4 ; 0,8 ; 1 )
IF((Projecten[mMargeFactor])>=4 && (projecten[mMargeFactor])<5 ; 0,9 ; 1 )
IF((Projecten[mMargeFactor])>=5 ; 0,7 ; 1 )
IF((Projecten[mMargeFactor])>=-1 && (projecten[mMargeFactor])<0 ; 0,4 ; 1 )
IF((Projecten[mMargeFactor])>=-2 && (projecten[mMargeFactor])<-1 ; 0,3 ; 1 )
IF((Projecten[mMargeFactor])>=-3 && (projecten[mMargeFactor])<-2 ; 0,2 ; 1 )
IF((Projecten[mMargeFactor])<=-3 ; 0,1 ; 1 )

how can i make this work?

[YvesL]

i tryed it like this

mMargecijfer =

SWITCH(true();

Projecten[mMargeFactor]>=5;1;

4<=Projecten[mMargeFactor]<5;0,9;

3<=Projecten[mMargeFactor]<4;0,8;

2<=Projecten[mMargeFactor]<3;0,7;

1<=Projecten[mMargeFactor]<2;0,6;

0<=Projecten[mMargeFactor]<1;0,5;

-1<=Projecten[mMargeFactor]<0;0,4;

-2<=Projecten[mMargeFactor]<-1;0,3;

-3<=Projecten[mMargeFactor]<-2;0,2;

Projecten[mMargeFactor]>-3;0,1)

i get this yellow bar:

In DAX comparisons, comparing values of the True / False type with values of the Integer type is not supported. Consider using the VALUE or FORMAT function to convert one of the values.

[Cmcmahan · Accepted solution]

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 

[YvesL]

this works,

so the i need to look to this as steps, if its false , continue until it finds a true or else

i got it

thx

[Anonymous]

Would the add conditional column functionality in power query work for you?

[Cmcmahan]

I don't see why it wouldn't.

[Cmcmahan]

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.