Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How can I exclude values from IF condition?

I have the following columns in my dataset. My code is 

IF(AND(TABLE[Value]>Table[Min], Table[Value]<Table[Max]), "In limits", ""OOL")

It checks value is between min/max and outputs in limits otherwise OOL (out of limits). However, some of my min/max values have an "NA" value instead of a number so returns error when I try doing comparisons with a number. Is there any way to just output "NA" instead of OOL/In Limits when either min or max value is "NA"?

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , I think because of variable data type. Ideally you should make all non numeric values as null in power query, so the column can have number data type

     

    still try this

    CL OOL =
    SWITCH (True(),
    Table[Min]="NA","NA",
    Panel_1[Max]= "NA", "NA",
    ISBLANK(Panel_1[Min]),"NA",
    AND(Panel_1[Value]*1>Panel_1[Min]*1,Panel_1[Value]*1<Panel_1[Max]*1),"In Limits", "OOL")

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try a new column like

     

    Switch(True() ,
    Table[Value] = "NA", "NA",
    isblank(Table[Value]) , "NA",
    AND(TABLE[Value]>Table[Min], Table[Value]<Table[Max]), "In limits"
    , "OOL")

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak 

       

      Tried the following code (It's the min/max columns tha have NA values, not the Value column)

       

      CL OOL = 
SWITCH (True(),
Table[Min]="NA","NA",
Panel_1[Max]= "NA", "NA",
ISBLANK(Panel_1[Min]),"NA",
AND(Panel_1[Value]>Panel_1[Min],Panel_1[Value]<Panel_1[Max]),"In Limits", "OOL")

       

       but it gives same error as before, "DAX comparison operations do not support comparing values of type Number with values of type Text." Last time, I had "NA" in my value column instead of Min/Max columns and used the below code (the or condition with index 91/92/93 etc... is just to exclude certain rows ). Is there a way to adapt the below code for the min/max columns instead of the value column? Thanks

       

       

      CL OOL = 
SWITCH (
    TRUE (),
    AND (
        IFERROR ( VALUE ( Panel_1[Value] ), "Not Running" ) >= Panel_1[Min],
        IFERROR ( VALUE ( Panel_1[Value] ), "Not Running" ) <= Panel_1[Max]
    )
        || Panel_1[Index] IN { 91, 92, 93, 94, 95, 96, 97, 98 }, "In Limits",
    IFERROR ( VALUE ( Panel_1[Value] ), "Not Running" ) = "Not Running", "Not Running",
    "OOL"
)

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous , I think because of variable data type. Ideally you should make all non numeric values as null in power query, so the column can have number data type

         

        still try this

        CL OOL =
        SWITCH (True(),
        Table[Min]="NA","NA",
        Panel_1[Max]= "NA", "NA",
        ISBLANK(Panel_1[Min]),"NA",
        AND(Panel_1[Value]*1>Panel_1[Min]*1,Panel_1[Value]*1<Panel_1[Max]*1),"In Limits", "OOL")