Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi BI Experts
I am relatively new to Power BI and excited about the potential that it has to help me with a project that I have been working on.
I am a volunteer for a local grassroots football club, which current has around 70 teams across all age groups and abilities. Whist not a charity as such, it is very much a community run club, and relies on volunteers like myself.
A little while ago, I posted a question about taking an over complicated fixture list created in MS Word and simplifying it in Power BI. Shortly after my post, and after a couple of day trying different things – I managed to get the results I needed.
Things have moved on now, and all the fixtures are created in in the FA system – Full Time. Whilst all the relevant fixtures are on one website, they are in sperate areas.
As an example, here is a URL of one of the leagues (teams from our club compete in 7 different leagues): http://fulltime-league.thefa.com/ListPublicFixture.do?divisionseason=719416464&league=841609
So, from within Power Bi - I have managed to collect all the data from the various sub-sites, added some of my own content and created one larger table - so all is good there.
I also have another table, that lists all 70 teams from within our club. I want to use this to populate a weekly fixture sheet, showing who has a match (not all teams play every week), who the home team, who the away team, kick of time etc. I will use a slider to select the date.
As I say, I have the content for this in a table, but my stumbling block is populating the weekly sheet.
I have tried a relationship between the Team Name on the club list and the “Home Team” in the fixture list – which seems to work, but I can’t add a second active relationship for team name/away team.
Thinks to bear in mind:
- A team may not have a fixture (so no record in the fixture table).
- I need to show the empty record on the weekly fixture sheet to show that there is no game.
- A team maybe home or away – so separate columns within the fixture table.
- As there are multiple teams within the club, so some fixtures will be our teams playing each other in a derby.
So the fixture table could look something like this …..
Club Team (from Club List) | Date (from Fixture Table) | Home Team (from Fixture Table) | Away Team (from Fixture Table) | Kick Off (from Fixture Table) |
U10 Team 1 | 22 Sep 19 | U10 Team 1 | U10 Random Opponent 1 | 12:00 |
U10 Team 2 | 22 Sep 19 | U10 Random Opponent 2 | U10 Team 2 | 10:00 |
U10 Team 3 | 22 Sep 19 | |||
U11 Team 1 | 22 Sep 19 | U11 Team 2 | U11 Team 1 | 13:00 |
U12 Team 2 | 22 Sep 19 | U11 Team 2 | U11 Team 1 | 13:00 |
Any thoughts or ideas on how to achieve this would be fantastic – if nothing more pointing me in the right direction. I think I am close, but it could also be something that isn’t possible.
Thanks in advance
Dean
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |