Measure to count rows in a related table with condition

[Kenmare]

Hi !

I have a problem to create a measure in power bi for this case :
- A table GAME which has a relation with table SETS (yes my project stores tennis games results as you can see))
- My measure has to find the number of victories (and defeats)

So a match is won if number of sets won by player 1 (games.player_1_games > games.player_2_games) > number of sets won by player 2.

In the following example player 1 has 1 victory 0 defeat and player 2 has 0 victory and 1 defeat.

Table MATCH

id	player_1_id	player_2_id
1	51	68

Table SETS

id	match_id	player_1_games	player_2_games
1	1	6	2
2	1	3	6
3	1	6	0

I tried to use SUMMARIZECOLUMNS witout success.
Is there a simple way to calculate this ? Maybe with countrows ?

Thank you by advance !

[ThxAlot · Accepted solution]

MATCH.pbix

I tweak one of my old examples for your reference. It's a bit tricky.

[Kenmare]

Thank you for your pbix file. 

I'll verify that but I think that can work with my example.

But your example seems to be tricky you're right 😀. For the Win Match (not in your file) I guess you've added a [set winner] column which can explain the propagation from VALUES (MATCH[Attribute]) to SETS[set winner] ?

I have to find a way to handle the case of grand slam because a match can be won be 3 sets to 2 ... 😅

[Sahir_Maharaj]

Hello @Kenmare,

Can you please try:

Victories = 
CALCULATE(
    COUNTROWS(SETS),
    FILTER(
        SETS,
        RELATED(GAME[player_1_id]) = EARLIER(GAME[player_1_id]) &&
        RELATED(SETS[player_1_games]) > RELATED(SETS[player_2_games])
    )
)

Should you need further assistance please don't hesitate to reach out to me.

[Kenmare]

Hello @Sahir_Maharaj 

Thank you for your answer.

The first problem is that the code (used in a calculated column) returns the error This Calculate returns EARLIER/EARLIEST refers to an earlier row context which doesn't exist

I tried to use a var to save current game[player_1_id] and use id instead of EARLIER. It solved the problem but the following error occurrs now on 

RELATED(SETS[player_1_games])

:

The column either doesn't exist or doesn't have a relationship to any table available in the current context.

Even a USERELATIONSHIP doesn't work.

My current column is defined like :

Victories = 
VAR current_game = SELECTEDVALUE(game[player_1_id])
RETURN 
CALCULATE(
    COUNTROWS(SETS),
    FILTER(
        SETS,
        RELATED(GAME[player_1_id]) = current_game &&
        RELATED(SETS[player_1_games]) > RELATED(SETS[player_2_games])
    ), 
)

I think I'm missing something ...

And other thing looks strange, if I want to count victories (for a game) maybe it would be better to have countrows on game and not on sets.

Kind regards