none
Help with Table Lookup and relationships RRS feed

  • Question

  • 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

    Thursday, September 19, 2019 7:46 AM

All replies