cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stephen1606
Frequent Visitor

Football League Table - Games Played

I've got a database of football stats which I have loaded into power bi and I'm trying to create a league table from them. 

 

The data has the stats for the home team listed as - "Home Team, Home Goals, Home Shots, etc." and then the equivalent for the Away team. 

 

I can't work out how to count how many games a team has played overall. I'm able to do two separate tables one for Home and one for Away but not a combined table.

 

In excel I would use the countif function, but I'm not sure how to do this using DAX. 

 

Any suggestions would be appreciated. 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Football League Table - Games Played

Hi @stephen1606,

 

In order to make the total games update according to the user selection on Slicers, you can use the formula below(just remove ALL in the FILTER of the formula provided by @erik_tarnvik above) to create a measure instead of calculate column. Smiley Happy

NoGames = 
COUNTROWS (
    FILTER (
        Games,
        Games[AwayTeam] = MIN ( Teams[Team Name] )
            || Games[HomeTeam] = MIN ( Teams[Team Name] )
    )
)

 

Regards

View solution in original post

8 REPLIES 8
erik_tarnvik Solution Specialist
Solution Specialist

Re: Football League Table - Games Played

Create a measure NoGames = COUNTROWS(GamesTable). Create a table visual and populate it with GamesTable[TeamName] and NoGames. Assuming you have one game per row in GamesTable this will provide the desired result.

 

if this answer doesn't hit the mark, please post a sample of your table(s). 

stephen1606
Frequent Visitor

Re: Football League Table - Games Played

Hi, 

 

Thanks for responding, unfortunatley that doesn't work as it only gives me the total number of games played either home or away.

 

Football Table.PNG

 

This is an example of my dataset. If I create a table visual I have to use either the HomeTeam or the AwayTeam column and which ever when I choose it only counts the games they played when either Home or Away. 

 

I created a new table which has unique values for all the teams but that doesn't work either as I can only create a relationship to one column and not both and therefore it only counts that field again...

erik_tarnvik Solution Specialist
Solution Specialist

Re: Football League Table - Games Played

I did not understand the structure of your data, thanks for the example. There are a couple of ways to solve this. Both involve creating a seaparate table with just the team names, let's call it Teams, with one column Teams[Team Name]. No relationships to your Games table.

 

Straightforward solution: create a calculated column in table Teams:

NoGames = COUNTROWS(Filter(Games, Teams[Team Name] = Games[AwayTeam] || Teams[Team Name] = Games[HomeTeam]))

This column will contain the total number of Games each team has played.

 

If you for some reason do not want a caculated column you can create a measure:

NoGames = Countrows(Filter(ALL(Games),Games[AwayTeam] = MIN(Teams[Team Name]) || Games[HomeTeam] = MIN(Teams[Team Name])))

 

Create a visual populated with Teams[Team Name] and the measure. The MIN function may seem odd but just retunrs the minum value from a vector of one element in this context. There is probably a better solution to this but couldn't think of one right now.

erik_tarnvik Solution Specialist
Solution Specialist

Re: Football League Table - Games Played

BTW the Teams table can be derived from the Games table. In "Modeling" in the data view, select "New Table" and enter

 

Teams = SUMMARIZE(UNION(SUMMARIZE(Games,Games[Away],"Team Name", [Away]),
SUMMARIZE(Games,Games[Home],"Team Name", [Home])),
[Team Name])

 

You can now add the calculated column to this new table or use the measure with this table as context in a table view.

stephen1606
Frequent Visitor

Re: Football League Table - Games Played

It's getting closer, thank you! I've now got the total games played for each team but because there is no relationship to the "games" table it means when I select a different year/season/league the total games doesn't update accordingly. 

 

It feels like it should be a simple task!

Microsoft
Microsoft

Re: Football League Table - Games Played

Hi @stephen1606,

 

In order to make the total games update according to the user selection on Slicers, you can use the formula below(just remove ALL in the FILTER of the formula provided by @erik_tarnvik above) to create a measure instead of calculate column. Smiley Happy

NoGames = 
COUNTROWS (
    FILTER (
        Games,
        Games[AwayTeam] = MIN ( Teams[Team Name] )
            || Games[HomeTeam] = MIN ( Teams[Team Name] )
    )
)

 

Regards

View solution in original post

erik_tarnvik Solution Specialist
Solution Specialist

Re: Football League Table - Games Played

Thanks @v-ljerr-msf. Beware of the lazy ALL Smiley Happy...

 

stephen1606
Frequent Visitor

Re: Football League Table - Games Played

That's perfect, thanks both for your help on this!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors