Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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,...
Need Help
Show and Tell
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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' ) )
)
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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

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's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

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

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

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