Power BI treating Blank as 0 when filtering

[mutendorf]

Hello, I am trying to use a field from a dimensional table as a filter in the report. When I use this field as column in the table, the values populate as expected. The error happens when I try to filter to 0. The table returns rows that have no value in the dataset (technically NULLS because there is no actual value in the associated table for these rows). I have discussed this with my team at work and we feel that PBI may have a bug when it comes to this. One solution for a workaround is to use Advanced Filter and select 'is 0' and 'is not blank', but this option is not ideal for our use case. Can someone please help? Screenshot below. Thanks!

[mutendorf]

Thanks for responding. The strictly equal to article was helpful and I was able to get a calculated column to work by incorporating IF [column] == BLANK() then 999 else [column]. This then assigns the previous blanks to 999 which can be filtered out. Thanks!

[DataInsights · Accepted solution]

@mutendorf,

Since BLANK() = 0 returns TRUE, it can cause unexpected behavior. One approach is to create a custom column "Amount Filter" in Power Query for filtering:

if [Amount] = null then "Blank" else [Amount]

Results:

Additional reading:

https://dax.guide/op/strictly-equal-to/