Forum Discussion
All/Removefilters not working with dates
Hello, I'm trying to count the number of distinct dates. The result should be 9 (see table below).
1) I tried the measure below, but the result is only the distinct count for each month, which is not right. I used the datefield in the matrix and the formula.
Mesure 2 = calculate(DISTINCTCOUNT(Table1[Date]),REMOVEFILTERS(Table1[Date]))
2) I created a month field in my table (see at the top), and used it in the formula and the matrix and it worked.
Mesure 2 = calculate(DISTINCTCOUNT(Table1[Date]),REMOVEFILTERS(Table1[Month]))
I don't understand why my first measure is not working. I don't want to use a month field because it is not sorting properly in the matrix (sorts alphabetically). I tried with ALL and the result was the same. What do I need to do to make it work with date field?
Thank you
There are a variety of ways to deal with this.
One option would be something like this:
Mesure2 = IF ( ISEMPTY ( Table1 ), BLANK (), CALCULATE ( DISTINCTCOUNT ( Table1[Date] ), REMOVEFILTERS ( 'Date' ) ) )
12 Replies
- AlexisOlson
Super User
In the first example, you aren't really using Date on the matrix rows but rather the month within the Date hierarchy, so clearing the filters on Date doesn't automatically remove the filters on the month part.
The best solution to this sort of problem is to use a proper date dimension table. Check out this article for more about why this is important and how to do it:
https://radacad.com/do-you-need-a-date-dimensionHello AlexisOlson , I already knew about date tables and I read your link to make sure I understood everything. I created a date table but the results are the same. Date is a hierarchy and monthname can't be sorted. Can you please elaborate on how to set-it up to make my measure work. Thank you
- AlexisOlson
If you have a date table, then you can use REMOVEFILTERS ( DateTable ) instead of REMOVEFILTERS ( DateTable[Date] ) and it should clear all of the date filter context.
- Ashish_Mathur
Hi,
Why can we not use this simple measure
Measure1 = distinctcount(Table1[Date])
Hope this helps.
- Ashish_Mathur
Share the link from where i can download your PBI file and show the expected problem very clearly.
PaulDBrownCommunity Champion
To sort the month chronologically you need to add a column in the table with the month number using
Month Number = MONTH (Table[Date])
you then select the month name column in the table, select the option "Sort column by" in the ribbon and choose the month number column. Now the month name field will be ordered chronologically in the visuals.
