cancel
Showing results for
Did you mean:
Helper II

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

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

2 ACCEPTED SOLUTIONS
Super User

@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")

Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!
Anonymous
Not applicable

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!
9 REPLIES 9
Anonymous
Not applicable

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!

Helper II

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

Anonymous
Not applicable

hi @helalm ,

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

Thanks!

Helper II

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
Not applicable

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!
Helper II

Perfect...Thanks a lot.

Helal

Anonymous
Not applicable

hi @helalm ,

Did this work?

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

Helper II

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
Super User

@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")

Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors