why does filtering for 0 includes nulls?

[nunnc01]

Hi,

I have a basic dataset where I wish to count how many records have a certain number in a specific column. 

I am using the formula:

# mymeasure = CALCULATE([# Contacts Families],FILTER('FD_REQUEST_SUPPORT',' FD_REQUEST_SUPPORT'[Duration] = 0))

where:

# Contacts Families = COUNT('FD_REQUEST_SUPPORT'[Contact Group ID])

If i report on Working Days = 0 it is incorrectly including nulls in the calculation. For any other number I choose to report on it gives me the correct figure and doesn't include nulls. Why is Power BI treating 0 and null as the same thing??

I found this question previously posted https://community.powerbi.com/t5/Desktop/null-0/td-p/274809 where a solution is offered but I'm trying to understand why I need to specifically mention nulls in my measure. It seems like an error that nulls are being included in the first place.

When I display my data as a matrix the numbers shown are correct - clearly splitting the counts of nulls and 0. It's just in the measure that it seems to be showing them together.

Can anybody offer any assistance?

[TomMartens · Accepted solution]

Hey,

the equality between 0 and BLANK() has been a hassle for quite some time, but with the June 2019 release of Power BI Desktop there is a new operator == (indeed 2 equal signs, this means strict). Here you will find more information about this:

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-june-2019-feature-summary/#dax

Please be aware that this new operator is only available in Power BI (Desktop and Service), if you are using Analysis Services on-premises or on Azure, this will not work. Just upate to the latest version of Power BI Desktop: https://www.microsoft.com/en-us/download/details.aspx?id=45331

Hopefully this is what you are looking for.

Regards,

Tom

[nunnc01]

Thanks for the reply.

Still strange to me that in Power BI = doesnt deal with 0's and nulls independently. In Excel countif would have excluded nulls in this example.

At least the new DAX operator you mentioned appears to resolve the issue. At the moment, I can't see any reason why I wouldn't use == for all my calculations going forwards.

Regards