Countrows not working properly with filters

[Anonymous]

Hi everyone,

I'm facing an issue with my DAX formula :

CALCULATE(COUNTROWS('ClothesSales'),REMOVEFILTERS('ClothesSales'[Color]))

It works fine when I don't apply any date filter on my visuals.

However, when i'm selecting a range period of time and apply the [Color] filter on the page, the function removefilters doesn't work anymore.

For example : I would select 1st of January to 31st, my total of clothes sales should by 300.

When applying a filter on the page : [Color] = red, the total should still be 300, since i'm using the removefilters. But it shows an inaccurate amount, like 285, which doesn't event represent the amount of red clothes.

When I'm not selecting any range period of time, applying a color filter doesn't seem to bug the formula.

I've tried using different DAX ways, but I can't find any that would make this work.

This also bugs with other filter functions : CALCULATE(COUNTROWS('ClothesSales'), 'ClothesSales'[Color] = "Red") would bug too.

Thank you for your help!

[Greg_Deckler · Accepted solution]

@Anonymous Easiest fix is to create a separate date table. Then you can grab the min and max of that table without it being impacted by the color filter.

[Anonymous]

@Greg_Deckler  I think this is the only way. A lot of work for just one measure, but as long as it works, I'm fine with it. Thank you for your help!

[Greg_Deckler]

@Anonymous Try:

Measure = COUNTROWS(FILTER(ALLEXCEPT(ClothesSales,'ClothesSales'[Color]),[Date]>=MIN('ClothesSales'[Date]) && [Date]<=MAX('ClothesSales'[Date])))

[Anonymous]

Hi @Greg_Deckler , thank you for your reply.

I tried this formula but same result 😪. In the meantime, I've been trying to create a calendar table and use a relationship between my sales dates and the calendar, then use a date slicer based on the calendar table : it works !
However I still don't know how I could create multiple date slicers with this solution (I have multiple date columns in my original table), since you can only create one relationship.

[Greg_Deckler]

@Anonymous Strange, the formula worked for me. See attached PBIX below sig.

[Anonymous]

@Greg_Deckler Strange indeed, even your pbix file doesn't work for me! It should be "8" and it shows "5"...