Forum Discussion
Help with IF Formula
Hello,
I have the following tableau formula, which I want to recreate in Power Bi desktop, but I do get an error message saying "too many arguments passed to the if function". Could you help me on that?
Tableau Formula:
if [GOAL)]<0.1 then 0
elseif [GOAL)] >= 0.10 and [GOAL)]< 0.25 then 0.5
elseif [GOAL)] >= 0.25 and [GOAL)]< 0.50 then 0.55
elseif [GOAL)] >= 0.50 and [GOAL)]< 0.75 then 0.65
elseif [GOAL)] >= 0.75 and [GOAL)]< 1.00 then 0.85
elseif [GOAL)] >= 1.00 and [GOAL)]< 1.20 then 1
elseif [GOAL)] >= 1.20 and [GOAL)]< 1.35 then 1.2
elseif [GOAL)] >= 1.35 and [GOAL)]< 1.50 then 1.35
elseif [GOAL)] >= 1.50 and [GOAL)]< 1.70 then 1.5
else 1.7
Power Bi Dax Formula :
IF([GOAL)]<0.1,0,
IF(AND([GOAL)]>=0.10,
[GOAL)]<0.25),0.5,
IF(AND([GOAL)]>=0.25,
[GOAL)]<0.50),0.55,
IF(AND([GOAL)]>=0.50,
[GOAL)]<0.75),0.65,
IF(AND([GOAL)]>=0.75,
[GOAL)]<1.00),0.85,
IF(AND([GOAL)]>=1.00,
[GOAL)]<1.20),1,
IF(AND([GOAL)]>=1.20,
[GOAL)]<1.35),1.2,
IF(AND([GOAL)]>=1.35,
[GOAL)]<1.50),1.35,
IF(AND([GOAL)]>=1.50,
[GOAL)]<1.70),1.5,"1.7")
Please use below If formula replacing Table with your table name and Column with your desired column name.
Column = IF(Table1[Goal]<0.1,0,IF(AND(Table1[Goal]>=0.1,Table1[Goal]<0.25),0.5,IF(AND(Table1[Goal]>=0.25,Table1[Goal]<0.5),0.55,IF(AND(Table1[Goal]>=0.5,Table1[Goal]<0.75),0.65,IF(AND(Table1[Goal]>=0.75,Table1[Goal]<1.0),0.85,IF(AND(Table1[Goal]>=1.0,Table1[Goal]<1.2),1,IF(AND(Table1[Goal]>=1.2,Table1[Goal]<1.35),1.2,IF(AND(Table1[Goal]>=1.35,Table1[Goal]<1.5),1.35,IF(AND(Table1[Goal]>=1.5,Table1[Goal]<1.7),1.5,1.7)))))))))Below is the result
6 Replies
- NishPatelResolver II
Please use below If formula replacing Table with your table name and Column with your desired column name.
Column = IF(Table1[Goal]<0.1,0,IF(AND(Table1[Goal]>=0.1,Table1[Goal]<0.25),0.5,IF(AND(Table1[Goal]>=0.25,Table1[Goal]<0.5),0.55,IF(AND(Table1[Goal]>=0.5,Table1[Goal]<0.75),0.65,IF(AND(Table1[Goal]>=0.75,Table1[Goal]<1.0),0.85,IF(AND(Table1[Goal]>=1.0,Table1[Goal]<1.2),1,IF(AND(Table1[Goal]>=1.2,Table1[Goal]<1.35),1.2,IF(AND(Table1[Goal]>=1.35,Table1[Goal]<1.5),1.35,IF(AND(Table1[Goal]>=1.5,Table1[Goal]<1.7),1.5,1.7)))))))))Below is the result - AnonymousNot applicable
HI use swith instead
SWITCH (TRUE,[GOAL)]<0.1, 0,[GOAL)]<0.25, 0.25,[GOAL)]<0.50, 0.50,etc...)- AnonymousNot applicable
Hi, I am not very sure I understand this switch formula, as I am trying to use if then else condition. Could you please advise how to do it with the IF AND formula , if possible?
- AnonymousNot applicable
Swith is doing the same as if on this example, it will read each value and give the final result automaticcaly
- AnonymousNot applicable
Thanks for providing me with additionnal info. I've already read it, but how to write with switch the condition between certain number and another number to accept some value. ( ex: elseif [GOAL)] >= 0.10 and [GOAL)]< 0.25 then 0.5). Thats why I tried to recreate it with IF(AND..
- AnonymousNot applicable
The switch wll test each value and if you put it in order asc you will hacve the good result
ex :
switch( TRUe,
exp1,0 ===>False
exp 2,1 this on switch will see if it is >1 and <2 automatically