Distinctcount entrys with date slicer

Question

this is probably pretty simple but I couldn't find any solution.

I have 2 tables: 'DateTime' and 'Usage' and I am using a date slicer (from to) which refers to 'DateTime'[Date]. Now I want to DISTINCTCOUNT 'Usage'[LPNumber] but only the ones which have a date that is included by the slicer. The table 'Usage' does also have a column with dates: 'Usage'[ConnectionStart Day]

I tried this but getting an error:

ActiveLP = 
VAR start_date =
    MIN ( 'DateTime'[Date] )
VAR end_date =
    MAX ( 'DateTime'[Date] )
RETURN
    CALCULATE(
        DISTINCTCOUNT( 'Usage'[LPNumber] );
        FILTER(
            'Usage';
            'Usage'[ConnectionStart Day] >= start_date
            && 'Usage'[ConnectionStart Day] <= end_date
        )
    )

The error says: A circular dependency was detected: DateTime[different columns]

Can someone please help me? Thank you very much :)

Strawberryshrub Strawberryshrub · Accepted Answer · 2019-12-03T12:14:57

Just create a realtionship between your DateTime table and your Usage table (by the date). Then use this simple measure:

Distinct Count = Distinctcount('Usage'[LPNumber])

If you put now a slicer (from the DateTime table) on your report and filter it, the other table also get filtered, because of the relationship. Thus the value of Distinct Count will change, according to your date slicer.

Distinctcount entrys with date slicer

1 Answers