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

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 123 2022-01-14 a b 4 3 124 2022-03-15 c a 3 2 125 2022-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
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.

3 REPLIES 3
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.

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.

Anonymous
Not applicable

Hi,

 game_id date home_club_id away_club_id home_club_goals away_club_goals 2219794 2012-07-22 58 498 3 2 2244388 2012-08-02 131 58 3 2 2211607 2012-08-05 58 610 4 2 2252846 2012-08-11 85 58 1 0 2229332 2012-08-12 58 16 2 1 2252859 2012-08-25 58 7179 0 1 2240246 2012-09-04 58 38 7 0

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

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.

Announcements

#### 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
Top Kudoed Authors