Football League Table - Games Played

[stephen1606]

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!

[erik_tarnvik]

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]

Hi, 

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

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]

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.

[stephen1606]

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!

[v-ljerr-msft · Accepted solution]

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. 

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

Regards

[stephen1606]

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

[erik_tarnvik]

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

[erik_tarnvik]

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.