How to use "AND" & "OR" in an IF statement excluding Blanks

[helalm]

I need help to use both "AND" or "OR" in an IF statement in DAX. Here is the formula that generates error:

Failure = if (and(Data[PERCENT1] >= 0,Data[PERCENT1] <= 85) || if(and(Data[PERCENT2]>= 0, Data[PERCENT2] <= 85) || if(and(Data[PERCENT3]>=0,Data[PERCENT3]<=85), "Fail", "Pass")

Basically, trying to determine pass/fail across three columns where a subject who has score <= 85% in any of the three, excluding BLANK, has a fail flag. THe >= 0 is not the best way to get rif of BLANK but it does work. 

Thank you

Helal

[amitchandak · Accepted solution]

@helalm , Try like

if ( and(Data[PERCENT1]+0 >= 0,Data[PERCENT1]+0 <= 85) || and(Data[PERCENT2]+0>= 0, Data[PERCENT2]+0 <= 85) || and(Data[PERCENT3]+0>=0,Data[PERCENT3]+0<=85), "Fail", "Pass")

[Anonymous]

hi @helalm 

Here you go

Failure = if (((Data[PERCENT1] >= 0 &&Data[PERCENT1] <= 85) || (Data[PERCENT2]>= 0 && Data[PERCENT2] <= 85) || (Data[PERCENT3]>=0 && Data[PERCENT3]<=85)), "Fail", "Pass")

Thanks!

[helalm]

Thank you...it looks like > 0 don't take care of BLANKS. Can BLANKS be assigned? like 'Failed', 'Passed','BLANKS'?

[Anonymous]

hi @helalm ,

Do you mean if any of Percent1/Percent2/Percent3 is Blank, assign the value as "Fail"?

Thanks!

[helalm]

hi @Anonymous ,

No, since it seems that blanks have already been accounted for in "Fail". I want to have a separate category as "Blanks". So, the group will be "Fail", "Pass", and "Blanks". Hope it makes sense.

Thank you

[Anonymous · Accepted solution]

hi @helalm 

Here you go

Failure =

if( (ISBLANK(Data[PERCENT1]) || ISBLANK(Data[PERCENT2]) || ISBLANK(Data[PERCENT3])), "Blank",if (((Data[PERCENT1] >= 0 &&Data[PERCENT1] <= 85) || (Data[PERCENT2]>= 0 && Data[PERCENT2] <= 85) || (Data[PERCENT3]>=0 && Data[PERCENT3]<=85)), "Fail", "Pass")

Thanks!

[helalm]

Perfect...Thanks a lot.

Helal

[Anonymous]

hi @helalm ,

Did this work?

If yes, Kindly mark my post as solution!!! Also would appreciate Kudos :)!!!

[helalm]

hi @Anonymous,

Yes, it did work. I am trying to get a variation of the formula and cross check with other variables. Here is the new code:

MSVTFail_IflmBias_chk =

if((ISBLANK(Data[MSVTCONSISTANCYPERCENT]) || ISBLANK(Data[MSVTDELAYEDRECALLPERCENT]) || ISBLANK(Data[MSVTIMMEDIATERECALLPERCENT]) ||

(Data[mBIAS_tot]=999)), "Blank",if (((Data[MSVTCONSISTANCYPERCENT] >= 0 &&Data[MSVTCONSISTANCYPERCENT] <= 85) || (Data[MSVTDELAYEDRECALLPERCENT]>= 0 && Data[MSVTDELAYEDRECALLPERCENT] <= 85) ||

(Data[MSVTIMMEDIATERECALLPERCENT]>=0 && Data[MSVTIMMEDIATERECALLPERCENT]<=85)) && (Data[mBIAS_tot]>=8) && ((Data[mBIAS_tot] = 999)), "MSVTF_InmBias", "Pass"))

The issue here is Data[mBIAS_tot] = 999 (eq missing) may have valid data across the firs three percent variables. the reverse is also true. Blanks on the three percent variables may have valid data on mBias score. The existing code doesn't check for all conditions. I don't know if this makes any sense to you. If not, pelase let me know so I an explain more. 

Thank you,

Helald 

[amitchandak · Accepted solution]

@helalm , Try like

if ( and(Data[PERCENT1]+0 >= 0,Data[PERCENT1]+0 <= 85) || and(Data[PERCENT2]+0>= 0, Data[PERCENT2]+0 <= 85) || and(Data[PERCENT3]+0>=0,Data[PERCENT3]+0<=85), "Fail", "Pass")