Showing results for 
Search instead for 
Did you mean: 
New Member

IF - OR Formula When Dealing with Null



I have columns "M2 Fail", "M3 Fail", "M4 Fail", etc.  1 means fail, 0 means pass.  The data for "M2 Fail" is pulled from Month 2, "M3 Fail" from Month 3, etc.


Right now, an input in null in a Month column is outputting a 0 (pass).


I want nulls to ouput a 1 (fail) in the corresponding month fail column, but only if there has already been a numerical value in a previous month.  For example:


-In row 1, Month 2 and Month 3 should count as 0's (passes) in columns M2 Fail and M3 Fail, because there hasn't been a numerical value in a previous month.  

Whereas in row 2, I want Month 4 and Month 5 to count as 1's (fails) since there has already been a numerical value in months 2 and 3.


How can this be accomplished?  Thanks in advance for your help!



Community Champion
Community Champion

Hi MikeY


You can write the below formula in your calculated column to achieve your desired result.


For M2 Fail= IF(AND(ISBLANK('Month Problem'[Month 2])=TRUE(),'Month Problem'[Month 1]>0),1,0)

For M3 Fail=IF(AND(ISBLANK('Month Problem'[Month 3])=TRUE(),'Month Problem'[Month 2]>0),1,0)

For M4 Fail= IF(AND(ISBLANK('Month Problem'[Month 4])=TRUE(),'Month Problem'[Month 3]>0),1,0)


Hope this will help you sir.


Thanks & Regards,screen 2.PNGscreen1.PNG



Thanks & Regards,

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.



We are not able to update the existing column values of M2 Fail, M3 Fail… in your table in powerbi desktop. We can add some calculated columns to do this.


I assume you have a table called “monthFail” like below.





You can use formulas below to create 5 calculated columns.

Since there is no column prior to Month 2, then M2-Fail should be always 0.


M2-Fail = 0


M3-Fail = if(monthFail[Month 2] <> BLANK(), if(monthFail[Month 3] = BLANK(), 1, 0), 0)


M4-Fail = if(monthFail[Month 2] <> BLANK() || monthFail[Month 3] <> BLANK(), if(monthFail[Month 4] = BLANK(), 1, 0), 0)


M5-Fail = if(monthFail[Month 2] <> BLANK() || monthFail[Month 3] <> BLANK() || monthFail[Month 4] <> BLANK(), if(monthFail[Month 5] = BLANK(), 1, 0), 0)


M6-Fail = if(monthFail[Month 2] <> BLANK() || monthFail[Month 3] <> BLANK() || monthFail[Month 4] <> BLANK() || monthFail[Month 5] <> BLANK(), if(monthFail[Month 6] = BLANK(), 1, 0), 0)


This is the result:




Best Regards



Helpful resources

Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors