Reply
Highlighted
Frequent Visitor
Posts: 6
Registered: ‎08-17-2018
Accepted Solution

Nested IF to create a column

I am trying to create a column using nested if statements but I get the following syntax error.

 

the original formula I want to replicate is:

 

=IFS(U11<2;"0-2";U11<4;"2-4";U11<6;"4-6";U11<8;"6-8";U11<=10;"8-10";U11>10;">10")

 

Thanks for help in advance.

 

Capture.PNG

 

 


Accepted Solutions
Super User
Posts: 621
Registered: ‎04-02-2017

Re: Nested IF to create a column

Try using "Conditional Column" option in Power Query Editor. It should easily this.

View solution in original post


All Replies
Super User
Posts: 621
Registered: ‎04-02-2017

Re: Nested IF to create a column

Try using "Conditional Column" option in Power Query Editor. It should easily this.

Super User
Posts: 859
Registered: ‎06-23-2016

Re: Nested IF to create a column

in DAX you can try wrapping the text in FORMAT function .e.g

FORMAT("0 - 2","")
Frequent Visitor
Posts: 6
Registered: ‎08-17-2018

Re: Nested IF to create a column

Thanks for the quick response. This solved my problem. @PattemManohar I tried using the FORMAT but still got an error. Have you tried it? Thanks

Regular Visitor
Posts: 28
Registered: ‎11-17-2017

Re: Nested IF to create a column

Try to create new custom column in Query Editor, like below.

 

(IF([COMP SCRAP]<=2) THEN "0-2" ELSE
IF([Comp Scrap]>2 AND [Comp Scrap]<=4) THEN "3-4" 

ELSE "")

 

(IF (U11<2) THEN "0-2" ELSE
IF(U11<4) THEN "2-4" ELSE
IF(U11<6) THEN "4-6" ELSE
IF(U11<8) THEN "6-8" ELSE
IF(U11<=10) THEN "8-10" ELSE
IF(U11>10) THEN ">10" ELSE "")

Super User
Posts: 859
Registered: ‎06-23-2016

Re: Nested IF to create a column

@lenon
I couldn't replicate your error, so I assumed FORMAT may help if for some reason your string is not considred text by PowerBI
this code works for me:

Column = IF([Comp Scrap]<= 2, "0 - 2",
    IF([Comp Scrap] > 2 && [Comp Scrap] <=4, "2 - 4",
        BLANK()
    ))