Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Countrows not working properly with filters

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's avatar
    Greg_Deckler
    4 years ago

    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.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous Try:

    Measure = COUNTROWS(FILTER(ALLEXCEPT(ClothesSales,'ClothesSales'[Color]),[Date]>=MIN('ClothesSales'[Date]) && [Date]<=MAX('ClothesSales'[Date])))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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!