All/Removefilters not working with dates

[Anonymous]

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

[PaulDBrown]

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.

[Anonymous]

Hello, yes I already tried that and it gives the wrong result as well. Thank you

[Ashish_Mathur]

Hi,

Why can we not use this simple measure

Measure1 = distinctcount(Table1[Date])

Hope this helps.

[Anonymous]

Hello, because it will give the same result as my measure in Example 1. Thank you

[Ashish_Mathur]

Share the link from where i can download your PBI file and show the expected problem very clearly.

[Anonymous]

Hello, the solution is now in the post. Thank you

[AlexisOlson]

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-dimension

[Anonymous]

Hello @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.

[Anonymous]

Hello @AlexisOlson , it does indeed but it creates another problem. It now populate months where I don't have any data. Thank you

Mesure 2 = calculate(DISTINCTCOUNT(Table1[Date]),REMOVEFILTERS('Date'))

[AlexisOlson · Accepted solution]

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' ) )
)

[Anonymous]

Thank you!