Forum Discussion
How to model soccer data ?
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?
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 🙂
Sure! The only thing a made up is reducing the dataset the minimum required and switch from local league handball to public soccer 😀
Please see attached the dataset with english headers ...
My issue with this is that the datamodel somehow is bad practice because different way of filtering cannot be accomplished with just a lean data table setup.
Remark: this is considered to be a proof of concept and not the final dataset. that's why power query steps are a little messy 😉
Datasat: https://1drv.ms/u/s!AhmA5aJ6VboGhOswCkcY0CS-PJ_TrQ?e=gTLS8B
First, Hi Germany 😉 and you can name it Football instead of soccer, am originally from Tunisia 😀.
Second, thank you for sharing your file because it's gonna be easier for me to help you.
Now, I have to say that your Model is not quite optimised
You need to look to obtain a Star Schema with correct Relationships. A Star Shema will make life easier to develop a good report.
So what is the issue you are facing? How can I help you? I still didn't understand what's your point! Can we do this step by step and show me by screenshots the desired Outcome?
