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
Anonymous
Not applicable

TOPN on two key columns as filter

Hi, 

 

As an introduction to the data and its features

This is an Football 'Games' table I am working with:

game_id     date     home_club_id       away_club_id      home_club_goals       away_club_goals
1232022-01-14       a       b       4      3  
1242022-03-15       c       a       3      2
1252022-06-16       a       g       1      6

 

 

My goal is to create a Measure like "Last 10 Games Total Goals".

 

So the key active relationship to my Club_lookup (in which I have information about clubs) table is home_club_id, so far I have been using  USERELATIONSHIP if wanted to take into account away_club_id. I managed to create Last "10 Home Games Total Goals" and "10 Away Games Total Goals":

 

Last 10 Away Games Total Goals = CALCULATE([Total Club Goals]*, TOPN(10, CALCULATETABLE(Games, USERELATIONSHIP(Games[away_club_id], Club_Lookup[club_id] ) ), Games[date], DESC))
 
I was thinking about solution like that:
 
Last 10 Games Total Goals = Last 5 Home Games Total Goals + Last 5 Away Games Total Goals
 
But it will not be accurate in case such as eg. 7 games from the last 10 was Away.
 
Any help would be appreciated. Thank you 🙂
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @Anonymous ,

 

To create a measure that calculates the total goals scored in the last 10 games, regardless of whether the games were played at home or away, you can use a combination of the FILTER and TOPN functions.

Here's one way you could write the measure:

Last 10 Games Total Goals =
CALCULATE (
    [Total Club Goals],
    TOPN (
        10,
        FILTER ( ALL ( Games ), Games[date] <= MAX ( Games[date] ) ),
        Games[date], DESC
    )
)


This measure first filters the Games table to include only rows where the date is less than or equal to the maximum date in the table. This ensures that only the most recent games are included in the calculation. It then uses the TOPN function to select the top 10 games by date, sorted in descending order. Finally, the measure calculates the total goals scored in those 10 games using the Total Club Goals measure.


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @Anonymous ,

 

To create a measure that calculates the total goals scored in the last 10 games, regardless of whether the games were played at home or away, you can use a combination of the FILTER and TOPN functions.

Here's one way you could write the measure:

Last 10 Games Total Goals =
CALCULATE (
    [Total Club Goals],
    TOPN (
        10,
        FILTER ( ALL ( Games ), Games[date] <= MAX ( Games[date] ) ),
        Games[date], DESC
    )
)


This measure first filters the Games table to include only rows where the date is less than or equal to the maximum date in the table. This ensures that only the most recent games are included in the calculation. It then uses the TOPN function to select the top 10 games by date, sorted in descending order. Finally, the measure calculates the total goals scored in those 10 games using the Total Club Goals measure.


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

 

But it will not be accurate in case such as eg. 7 games from the last 10 was Away.

 

Please explain.   When I hear "Last 10 Games" I think of a date filter/sort, and do not consider if these games were home or away games. Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Anonymous
Not applicable

Hi,

 

game_id      date    home_club_idaway_club_idhome_club_goalsaway_club_goals
2219794     2012-07-22   5849832
2244388     2012-08-02   1315832
2211607     2012-08-05   5861042
2252846     2012-08-11   855810
2229332     2012-08-12   581621
2252859     2012-08-25   58717901
2240246     2012-09-04   583870


Lets take 5 last games so it is easier. 

The club we are taking as filter is 58. The problem is when I filter the TOPN function by date it only looks at the games that have the home_club_id as 58 because that is the key active relationship between 'Club' and 'Games' Tables.

 

What I want is:

Total Goals Last 5 games = 12

 

Instead, using:

Last 5 Games Total Goals = CALCULATE([Total Club Goals], TOPN(5, Games, Games[date], DESC ))
 
I get this:

Total Goals Last 5 games = 16

 

Because it takes into account only home games.


Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors