Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Kenmare
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

idplayer_1_idplayer_2_id
15168

 

Table SETS

idmatch_idplayer_1_gamesplayer_2_games
1162
2136
3160

 

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

Thank you by advance !

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

MATCH.pbix

 

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

ThxAlot_0-1689545347698.png

 

ThxAlot_2-1689546779988.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

4 REPLIES 4
ThxAlot
Super User
Super User

MATCH.pbix

 

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

ThxAlot_0-1689545347698.png

 

ThxAlot_2-1689546779988.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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
Super User
Super User

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.


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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors