cancel
Showing results for
Did you mean:
Highlighted
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

## 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.

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

Regards

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

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.

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

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.

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.

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

## 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.

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

Regards

Solution Specialist

## Re: Football League Table - Games Played

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

Frequent Visitor

## Re: Football League Table - Games Played

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

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

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

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors