cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
helalm
Helper II
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
amitchandak
Super User
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 !!

View solution in original post

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!

View solution in original post

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!

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!

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!

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 :)!!!

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

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

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 â__ December 6-8, 2022

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.