DAX: Get distinctcount from other table based on value in current table

Question

I'm struggling with DAX, and want to complete the simple task of counting the number of games hosted by a given player (one table) based on the distinct number of rows in another table (game_instance).

Tables:

game_instance (id, owner_id, x, y, z)
player (id, x, y, z)

My Measure is as such:

Hosted Games = CALCULATE(DISTINCTCOUNT(game_instance[id]), FILTER(game_instance, game_instance[owner_id] = [id]))

I'm getting blank, but I know this not to be true, what is wrong with this measure.

I cannot create a relationship, as there is already a chain relationship, so powerbi will not allow me to make a direct one.

sergiom sergiom · Accepted Answer · 2021-02-03T16:01:30

The main problem with the DAX measure you posted is that the [id] is referring to the game_instance[id] and not to the player[id].

In order to get a correct result it's needed to save the current player id to a variable to be used later in the filter iteration. To make the measure work also for the totals, a SUMX can be added over the player[id] in the current selection (this means just one when the player is selected on the row, or all the players for the total)

This measure might work

Hosted Games =
SUMX (
    VALUES ( player[id] ),
    VAR CurrentPlayerID = player[id]
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( game_instance[id] ),
            FILTER ( ALL ( game_instance ), game_instance[owner_id] = CurrentPlayerID )
        )
)

DAX: Get distinctcount from other table based on value in current table

1 Answers