TOPN on two key columns as filter

[Anonymous]

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 🙂

[v-henryk-mstf · Accepted solution]

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]

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]

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

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.

---