Forum Discussion

JCMT's avatar
JCMT
Frequent Visitor
1 year ago
Solved

Rolling DistinctCount + Filters

Hi All,    My dataset has a calendar table and a table containing records with various properties that are linked by date to the calendar table, I've setup a measure to output a rolling total count...
Need Help
  • JCMT's avatar
    JCMT
    1 year ago

    Still haven't identified exactly what the issue was but it seems it was something in the source dataset because when I started from scratch with a fresh copy of the data and re created the measures it all behaved as expected. 

     

    Ultimately I wanted a rolling average, here are the final measures: 

    Unique Record IDs = COALESCE(DISTINCTCOUNT(Records[ID]), 0)


Unique Record IDs Rolling 7D Average =
DIVIDE(
    CALCULATE(
        SUMX(
            VALUES('Calendar'[Date]), [Unique Record IDs]
            ),
            REMOVEFILTERS('Calendar'[Date]),
            'Calendar'[Date] >= (max('Calendar'[Date])-6) && 'Calendar'[Date] <= max('Calendar'[Date])
            ),
        7
)

     

SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

Hi JCMT 

Three things to note:
AllSelected removes internal filters, so the filters caused by the date row inside of the visual.

All removes all filters on the given table/column both internal (visual) and external (slicer).

This could be part of the misunderstanding.

 

Secondly:

Calculate lets you change, replace or remove filters.

Filter keeps the existing filters and adds to them.

So this could also be a cause of your issue.

 

Finally applying a filter to one of the fields in the Record table

Removing/adding filters to the calendar table will affect the Record table.

However if the filter is on some other column for example RecordType this wouldn't be removed by the AllSelected here.

You could use something like AllExcept(Record, Calendar[Date]) to remove all filters on the Record table but keep the filters on the Calendar table.

 

What are the filters you are adding and which do you want to keep?

JCMT's avatar
JCMT
Frequent Visitor
1 year ago

Thank you very much for your response..

 

Finally applying a filter to one of the fields in the Record table

Removing/adding filters to the calendar table will affect the Record table.

However if the filter is on some other column for example RecordType this wouldn't be removed by the AllSelected here.

You could use something like AllExcept(Record, Calendar[Date]) to remove all filters on the Record table but keep the filters on the Calendar table.

 

What are the filters you are adding and which do you want to keep?

I want to keep the filter on the Record table (eg: RecordType) and remove any implicit filtering on the Calendar table. 

 

I can't paste raw data but can generate a mock up if that's needed to help further.