CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
NeedHelpWithEverything
NeedHelpWithEverything

Reputation: 41

Distinctcount entrys with date slicer

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

Upvotes: 0

Views: 592

Answers (1)

Strawberryshrub
Strawberryshrub

Reputation: 3389

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.

Upvotes: 0

Related Questions