Counting an analyzing all references in another table

[AlexTimefire]

I have a table CHAR_Characters
One of the fields is PlayerId, this is a unique key

I have another table Events_SI_PlayerOffline
It has a field PlayerId (Matches PlayerID from CHAR_Characters)
It also has a field for Duration,
Finally, it has a field for EventDate


They are setup in a one to many (1:*) Relationship of one CHAR_Characters to many Events_SI_PlayerOffline

I can easily chart the average Duration for each date. But if I wanted to say, get the average Duration for each date, for each PLAYER, I run into trouble.

For example, if I have an entry in CHAR_Characters for PlayerID 345
And on 1/29/2019 there are three entries in Events_SI_PlayerOffline, with Player ID 345, and following durations: 1, 1, 10

Assuming PlayerID 345 is the only PlayerID with entries in Events_SI_PlayerOffline for that day
The average Duration on that day would be 4 minutes. I can get this easily
However, the Average Duration on that day PER PLAYER would be 12 minutes, but I do not know how to currently calculate and chart that.

It should be noted I am using DirectQuery, as we have a very large, and continously expanding dataset.

[v-juanli-msft · Accepted solution]

Hi @AlexTimefire

Create measures

Measure =
CALCULATE (
    SUM ( Events_SI_PlayerOffline[Duration] ),
    ALLEXCEPT (
        Events_SI_PlayerOffline,
        Events_SI_PlayerOffline[EventDate],
        Events_SI_PlayerOffline[PlayerId]
    )
)

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( Events_SI_PlayerOffline[PlayerId] ),
    ALLEXCEPT ( Events_SI_PlayerOffline, Events_SI_PlayerOffline[EventDate] )
)


Measure 3 = [Measure]/[Measure 2]

Best Regards

Maggie

[AlexTimefire]

Thanks you so much Maggie! Now I can dig down and figure out exactly how these measures work, so I can apply this kind of behavior to other tables.