DAX: Average distinct values per day

Question

I've got some categories spread over a number of days. The same category can occur several times on the same date. How can I get the average number of distinct categories per day?

Dataset:

Date,Category
11.10.2018,A
11.10.2018,B
11.10.2018,C
12.10.2018,A
12.10.2018,A
12.10.2018,A
13.10.2018,B
13.10.2018,B

Table from data view:

Table visualization:

My attempt:

I'm able to get distinct values per day as a measure using dist = CALCULATE(DISTINCTCOUNT(Table1[Category]);DISTINCT(Table1[Date]))

So what I'd like to end up with is the average of dist in the table above which is 1.67. But how do you do that? I've tried different combinations with AVERAGE, AVAREGEX, VALUES and CALCULATE, but with no luck. And the more I try, the more I convince myself that DAX is useless (even though I know deep down it can't be). Thank you for any suggestions!

Olly Olly · Accepted Answer · 2018-10-11T08:17:05

Use SUMMARIZE to calculate the distinct count for each date, then you can use AVERAGEX to iterate over each date value:

dist:= 
IF ( 
    HASONEFILTER ( Table1[Date] ), 
    DISTINCTCOUNT ( Table1[Category] ), 
    AVERAGEX ( 
        SUMMARIZE ( 
            Table1, 
            Table1[Date],
            "Daily Average", DISTINCTCOUNT ( Table1[Category] )
        ),
        [Daily Average]
    )
)

EDIT: You don't really need the IF function - it seems to perform just as well using simply:

dist:= 
AVERAGEX ( 
    SUMMARIZE ( 
        Table1, 
        Table1[Date],
        "Daily Average", DISTINCTCOUNT ( Table1[Category] )
    ),
    [Daily Average]
)

DAX: Average distinct values per day

1 Answers