Blank values in CALCULATE() filter are not excluded

[Anonymous]

We have a CALCULATE function similar to this:

CALCULATE(SUM('procurement'[procurement]); RELATED('partnerMeta'[inn]) = 'Data'[inn])) 

However if partnerMeta[inn] is empty (blank, null, whatever), we expet those rows not to be included in the result. Instead, in case when 'Data[inn]' does not have a match in 'partnerMeta'[inn], we see the same result = a sum of all rows in procurement[procurement] where inn is blank

How to make comparison in filter work normally. e.g. blank/null should not equal an actual value?

[mahoneypat]

I would have expected your approach to work too.  If it is not, you could consider making it an AND() like

CALCULATE(SUM('procurement'[procurement]); AND(RELATED('partnerMeta'[inn]) = 'Data'[inn], NOT(ISLBANK(RELATED('partnerMeta'[inn])))))

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

We tried different syntax. This is not about syntax, this is about how the platform handles comparison when one of the values is null (or some variance of null).

[v-juanli-msft]

Hi @Anonymous 

Is any of answers helpful?

How about add NOT(ISBLANK(partnerMeta[inn])) in the filter?

If all of the answers are not helpful, please check the data type in "Transform datat", check if your blank cells are of null type or any other.

It would be helpful to reproduce your problem with some sample data or screenshots.

Best Regards

Maggie

[Anonymous]

We tried adding NOT(ISBLANK(...)). The outcome is the same.

Question is, why comparing a blank/null value to actual value evaluates as true?

[speedramps]

Please consider this solution which adds zero

This will return blank in 2020 because there is no procurement yet in 2021:-
Total 2021 qty = CALCULATE(SUM('procurement'[qty]),Calendar[Year]=2021)

but this will retun 0 in 2020 even if there is no procurement yet in 2021:-
Total 2021 qty  with zero = Total 2021 qty + 0

[Anonymous]

Let me rephrase the question.

Is it normal that comparing null/blank value to an actual value results in TRUE within the filter expression of calculate?