Forum Discussion
How can I fix this error in DAX code?
The below is a column created with DAX, which checks whether the value in the "Value" column is more than the "Min" column and less than the "Max" column, and will will display "In Limits" if this is the case and "OOL" otherwise. I have also excluded rows 91/92/93/94, which will always display In Limits. However, a problem arises because sometimes the value column does not have a decimal number but the string "NA" instead which returns an error when it tries to do the greater/less than operation. Is there a way to write a condition so that if it is NA in the value column, just mark it as "Not Running" rather than in limits/ool? i.e. run the below condition, but if Panel[Value]="NA", don't run the below condition and just mark CL OOl column with "not Running"
CL OOL = IF(AND(Panel[Value] >= Panel[Min], Panel[Value] <= Panel[Max]) || Panel[Index] = 91 || Panel[Index] = 92 || Panel[Index] = 93 || Panel[Index] = 94 ||, "In Limits", "OOL")
Anonymous , Try this one:-
CL OOL = SWITCH ( TRUE (), AND ( IFERROR ( INT ( Panel[Value] ), "Not Running" ) >= Panel[Min], IFERROR ( INT ( Panel[Value] ), "Not Running" ) <= Panel[Max] ) || Panel[Index] IN { 91, 92, 93, 94 }, "In Limits", IFERROR ( INT ( Panel[Value] ), "Not Running" ) = "Not Running", "Not Running", "OOL" )
8 Replies
Hi Anonymous ,
Pls try this:-
CL OOL = SWITCH ( TRUE (), AND ( INT ( Panel[Value] ) >= Panel[Min], INT ( Panel[Value] ) <= Panel[Max] ) || Panel[Index] IN { 91, 92, 93, 94 }, "In Limits", Panel[Value] = "NA", "Not Running", "OOL" )Thanks,
Samarth
Hi Samarth_18 ,
Your code gave this error: "Cannot convert value 'NA' of type Text to type Number". My Value column has type text because it holds both numbers and "NA" values.
Anonymous ,Pls try now:-
CL OOL = VAR result = IF ( AND ( INT ( Panel[Value] ) >= Panel[Min], INT ( Panel[Value] ) <= Panel[Max] ) || Panel[Index] IN { 91, 92, 93, 94 }, "In Limits", "OOL" ) RETURN IFERROR ( result, "Not Running" )
Anonymous ,Try this code:
= SWITCH(TRUE(),
Value>=Min && Value<=Max,"In Limits",
Panel[Index] = 91 || Panel[Index] = 92 || Panel [Index] = 93 || Panel[Index] = 94,"In Limits",
Value="NA","Not Running","OOL")Hi Tahreem24 , it gave the error "The syntax for '&&' is incorrect". Here is a picture of my data if it helps. Ignore columns with cross.
