cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure to count rows in a related table with condition

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 ?

1 ACCEPTED SOLUTION
Super User

MATCH.pbix

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

4 REPLIES 4
Super User

MATCH.pbix

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Frequent Visitor

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 ... 😅

Super User

Hello @Kenmare,

``````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.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Frequent Visitor

Hello @Sahir_Maharaj

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