How to model soccer data ?

Question

Hi all,I've got a short data modeling questions for a soccer dataset. Tables look like this:Matches:matchID | homeID | awayID | ...&nbsp;Teams:teamID | Name | ...&nbsp;Events:eventID | matchID | EventNow, to my question:The events table contains all tracked events (eg goal or yellow card). Based on this, I am calculating some stats. Now, I want to create a dashboard with a slicer to select the team. Based on the slicer all games with the team being involved are shown and stats are calculated.&nbsp;But how do I link the "teams" table to two columns in the "matches" table?&nbsp;One could say: unoivot the data and you've got two entries per match, one for home and one for away. But if so, how to link the ticker table then other than n:m?&nbsp;happy to hear your advice!nico&nbsp;

aj1973 · Answer

Hi Nico,Can you share the three Tables please!

nwrozyna · Answer

Thanks for the quick reply. I don't have the real data with me because I'm on the go. but basically it's like this:&nbsp;

aj1973 · Answer

Hi&nbsp;nwrozyna&nbsp;You should have another table with the PlayerID, PlayerName and PlayerTeam. Like Salah is a Liverpool Player or Sterling is Man City...Then in event Table you would add a Column for Event name like (Yellow Card, Red Card,Goal,Sub...) and another column with the name of the player or even better his ID.I am sure these infos are available somewhere in Google.Good luck

nwrozyna · Answer

Thanks! Got until that point. This is what the real dataset actually looks like. Now two scenarios that I struggle with:

Scenario 1)

Select a team slicer filtering dimension table "Teams" with e g Liverpool to show only games (incl. results and some game metadata) that Liverpool is playing (either home or away). How to deal with it other than changing the data structure of the matches table into something like that:

matchID | homeOrAway | teamID

m10001 | home | t1

m10001 | away | t2

With this I can filter the teamID column to let only games appear where t2 (Liverpool) is playing.
What's best practice when it comes to storing other match metadata like result, date, location? I could store it into another table with matchID as column with unique value (1*n relationship).

Scenario 2:

I want to browse some key stats out of the events table. But only for a specific game Liverpool was playing. I can only achieve this by building an n:m relationship between the newly created unpivoted matches table and the events table (via matchID).

What's your suggestion on this?

aj1973 · Answer

My suggestion is that you send me your PBIX file so you can save time for and for you.My guessing is that you are trying to build a Dataset off your guessing 🙂&nbsp;

Forum Discussion

How to model soccer data ?

9 Replies

Related content

Data Modeling

Data Modelling Issue

Data Modelling

Open the data model

DAX measures Clean-up in data model?