IF - OR Formula When Dealing with Null

[MikeY]

Hello,

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!

[BhaveshPatel]

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,

Bhavesh

[AlexChen]

Hi,

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

Alex