Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Total games played!

I've got a data of football statistics which I have loaded into power bi.

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

 

I can't work out how to CALCULATE Win,loose,draw and how many games a team has played overall. I'm able to do two separate tables one for Home (home win,home loss ets..) and one for Away(Away win,Away loss) but not a combined table.


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


I have attached sample 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.
I even tried with bridge table with key column that doesn't work either.

Any suggestions would be appreciated.


Thank you

Need Help

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    One option would be to do some manipulation in Power Query. First, add an index column called Match ID. Then create a new query which references the first, called Home Games. Use the Choose Columns functionality to select only the columns which relate to the home team, and add a new column "Home / Away" which simply contains "Home".

    Repeat the above, again referencing the original query, but this time choosing the columns for the away team, and setting the Home /  Away column to Away.

    You can now append the new queries so you have 1 table, and all your measures will only need to look at a single column, and you can filter for home or away.

    You can right-click on the original query and untick Enable Load so that it doesn't appear in your report, it will only be used to populate the appended queries.