Forum Discussion

MikeY's avatar
MikeY
New Member
10 years ago

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 nu...
Need Help
Tutorial Requests
AlexChen's avatar
AlexChen
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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