Forum Discussion
COALESCE
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
7 Replies
Anonymous , 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)Hello
I don't understand what you mean by this message.
Anonymous
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 🫡👍
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.
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 🫡👍
