!IsNull and !IsNull Not Working

[giannademetroulakos]

I'm trying to filter out rows where the data for all months (columns) are either blank or 0. Below is the formula I currently have, but it appears to be filtering out rows where any column is null or zero. 

Thank you!

[binuacs · Accepted solution]

@giannademetroulakos Use isEmpty() function instead of Null() function, the isEmpty function will check both null and banks values. Also not that NULL , blank and 0 are different values, the isNull() function only checks whether the data is Null or not. If you want to check for 0 you need to add [OCT_FY22] != 0 also in the condition

[DataNath · Accepted solution]

Hey @giannademetroulakos, adding the ! in front of IsNull() turns it into NOT IsNull(). Therefore, your current expression is checking that every single month isn't null, hence why you'll be removing records where any of the months are 0. If you only want to lose records where all of the months are null or 0 then you'll need to add another check to each month for example:

(!IsNull([OCT_FY22]) or [OCT_FY22] = 0)

AND (!IsNull([NOV_FY22]) or [NOV_FY22] = 0)

and so on...

If you want to reduce the size of your expression or make it more dynamic/futureproof, you can also look into transposing your data, grouped on RecordID and filtering based on counts of nulls vs months etc.

[giannademetroulakos]

How would I incorporate checking for zeros for each month? I want to make sure I am only filtering out values where for every single month the value is either blank, null or zero. Thank you. 

[DataNath]

@giannademetroulakos for each month you'd want to write out something like:

(!IsEmpty([OCT_FY22]) AND [OCT_FY22] != 0)
AND
(!IsEmpty([NOV_FY22]) AND [NOV_FY22] != 0)
AND
...

  Haven't wrote it all out as it's repetitive but you'd just want to do that for them all.

[giannademetroulakos]

Thank you!