COALESCE

[storres1]

I have two tables, one of them with the year dimension and the other contains information, in addition to a column with the years, related to one to many. I use two measures, the first is to count COUNT() rows and the second I do COALESCE(COUNT(),0) in case there is a null value. The curious thing is that the DAX measure with COALESCE() ignores the filter that I make through the year dimension, and gives me an option that is not possible. It's like it ignores the filter and still

[Daniel29195]

@storres1 

Hello, 

what power bi does, 

First it creates a crossjoin between the dimensions. 

Then it calculates the measures the measure,  

since you select the filter =  2023 , then for 2022, it returns  blank  , that is why , the rows disappear from the visual. 

now , since coalesc, returns 0 , which is a value and not blank () , that is whym it seems like it not filtering . 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[Syndicate_Admin]

Hello

What I don't understand is, in the measure filter context is applied first, that way the 2022 rows should not be taken into account in the measure. I do understand that if the combination exists but returns blank then I give it 0. But I'm applying the measure to a row that shouldn't appear because filter context is applied before.

[Daniel29195]

@Syndicate_Admin 

can you please check that show data with no items is unchecked ? 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[Syndicate_Admin]

Hello

No, the option is not checked

[Daniel29195]

@Syndicate_Admin 

can you please share the file so i can take a look . 

[amitchandak]

@storres1 , You have try measure like examples

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
if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1)

or

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

[Syndicate_Admin]

Hello

I don't understand what you mean by this message.