When adding IF/COALESCE logic to change blanks to zeroes in a measure, the filter context is removed

[davv36]

When adding IF/COALESCE logic to change blanks to zeroes in a measure, the filters are being removed completely on the visuals using those measures!

It is effectively changing

Measure = SUM(table[column]) 

to

Measure = COALESCE(SUM(table[column]),0)

or the same with an IF instead.

[Anonymous]

@amitchandak , Hi ! Sorry to activate an alreay answered question but I have a similar problem. But instead of a date filter my filter is 'Department'( Salesperson from different department) , how do I change the above mention dax. minx, maxx doesn't work here obviously. Is there a solution? 

[amitchandak · Accepted solution]

@davv36 , +0 or coalesce can remove context. So in case of date I pushed it back like

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

[Anonymous]

Sorry about raising an older question, but I am trying to understand the why with this.

I had a similar issue where the filtering was removed on a Matrix table, with multiple rows, where I did a COALESCE to default to zero.

I applied your fix above and yes, it fixed the problem.

But the question still remains, why?  Why does DAX behave like this?

[sergiotb2001]

Hi, I have the same issue. I don't know why remove the context of the filter using COALESCE. I have one table with dates, and more columns, I filter the table by year and with COALESCE(COUNT(),0) I count the number of rows but it appears as 0 rows with dates in 2022

[sergiotb2001]

When I have filtered the table by 2023

[davv36]

Thank you - works perfectly!