cancel
Showing results for
Did you mean:
New Member

## IF - OR Formula When Dealing with Null

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.

2 REPLIES 2
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)

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Microsoft

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

Announcements

#### Power BI Community Changes

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

#### Power BI May 2023 Update

Find out more about the May 2023 update.