Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Nested IF with AND

I wish to add column in the query based on flag in the incoming data.
To achieve is:
based on flag value :

if flag < 1, then Red
else if flag is >0 and flag < 3 then yellow
else green

I tried to do it with DAX function as below : ([D] is my flag columns

 

IF (AND([D] >0,[D]<3),"Yellow",IF([D] < 1,"Red","Green"))

 

Error:

 

 

Tried Switch function too:

 

= SWITCH(TRUE(),
   [D] <= 0, "Red",
   AND([D] >0, [D] < 3), "Yellow",
   "Green")

 

Error:

 

  • Anonymous In Power Query, make sure you are following case-sensitive rules. As M-code is case-sensitive and IF is not same as if.

     

    Please change the logic in Power Query as below

     

    = Table.AddColumn(#"Added Index1", "Custom", each if [Flag]<1 then "Red" else if [Flag]>0 and [Flag]<3 then "Yellow" else "Green")

    In DAX, it will be

     

    IF(Test154[Flag]<1,"Red",IF(AND(Test154[Flag]>0,Test154[Flag]<3),"Yellow","Green"))

2 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion

    Anonymous In Power Query, make sure you are following case-sensitive rules. As M-code is case-sensitive and IF is not same as if.

     

    Please change the logic in Power Query as below

     

    = Table.AddColumn(#"Added Index1", "Custom", each if [Flag]<1 then "Red" else if [Flag]>0 and [Flag]<3 then "Yellow" else "Green")

    In DAX, it will be

     

    IF(Test154[Flag]<1,"Red",IF(AND(Test154[Flag]>0,Test154[Flag]<3),"Yellow","Green"))
    • Anonymous's avatar
      Anonymous
      Not applicable

      Ola..!! It workd for me. Thanks