cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Populate table when multiple relationships exist between tables

Hi!  This is kind of an academic project...I have data for the game results for the NFL 2018 season along with tables for individual teams and stadiums.
Capture.PNGData model
Two relationships exist between Teams and Team Scores by Week:
- Team(Team ID) --> Team Scores by Week(Home ID) - this is the active relationship
- Team(Team ID) --> Team Scores by Week(Away ID) - inactive

There is a bi-directional relationship between Teams and Stadiums
When trying to populate a table with the game by game results, only the home game results are shown because that is the active relationship.
Capture.PNGTable results
How can I populate this table with the games where the Bears (in this case) are the visiting team?  Open for any suggestions/ideas that anyone could provide.  Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Populate table when multiple relationships exist between tables

And TA-DA!!!

 

Capture.PNG

 

Thank you for helping me think it through!

9 REPLIES 9
natelpeterson New Contributor
New Contributor

Re: Populate table when multiple relationships exist between tables

@littlemojopuppy  - One option is to create a copy of the teams table, specifically to identify the Visitor. You can create a Calculated Table like:

Visiting Team = Teams

Cheers!

Nathan

Re: Populate table when multiple relationships exist between tables

I attempted to copy the teams table but that created a host of problems with maintaining the proper filtering, making sure relationships worked in the model, etc.

This is the best I've been able to come up with...create a table like this:

Selected Schedule = 
    VAR
        SelectedTeam = 
            IF(
                ISCROSSFILTERED(Teams[TeamID]),
                SELECTEDVALUE(Teams[TeamID]),
                BLANK()
            )
    RETURN

    FILTER(
        'Team Scores by Week',
        OR(
            'Team Scores by Week'[HomeTeamID] = SelectedTeam,
            'Team Scores by Week'[AwayTeamID] = SelectedTeam
        )
    )

I created a measure with just the variable part of the code to test whether it was correctly capturing the changes in selection.  It is.
Capture.PNGCapture1.PNGCapture2.PNG

 

The problem with this approach is that the table won't recalculate whenever selections change.

Re: Populate table when multiple relationships exist between tables

Forgot to include results of the creating the table...

Capture.PNG

 

I don't believe there is another way to interactively calculate a table and then use those results to populate a table...

Re: Populate table when multiple relationships exist between tables

One last thing...if I substitute a value for Team ID into the FILTER function (such as 6 for Chicago Bears) it produces exactly what would be necessary...
Capture.PNGHard coded Team ID example

natelpeterson New Contributor
New Contributor

Re: Populate table when multiple relationships exist between tables

@littlemojopuppy - Calculated Tables and Calculated Columns are created at data load time, not at report query time. That's why it is responsive to the hard-coded value but not the filter.

 

A measure could be useful, as rows can be hidden if they only include BLANK measures.

 

Hope this helps,

Nathan

 

 

Re: Populate table when multiple relationships exist between tables

I realized shortly after I posted my responses that if I want interactive calculation, I have to use measures and I'm trying to create some measures right now.  Created a table called Master Schedule that consists of Season and Week (2018, 1; 2018, 2; etc.)  Used the week number from Master Schedule to populate week in the report.  But strangely the measure is still pulling only home games...

Capture.PNG

And I just realized why: I'm filtering by team, which still has the primary relationship to Home Team ID.  Going to stick an ALL in the measure and see what happens.

 

But the good news is that I think I'm close...

Re: Populate table when multiple relationships exist between tables

And TA-DA!!!

 

Capture.PNG

 

Thank you for helping me think it through!

Re: Populate table when multiple relationships exist between tables

One last question...

Wrote my measures and they're all working perfectly except one.  Here's the code to retrieve the stadium name:

Home Stadium = 
    VAR
        StadiumID = 
            CALCULATE(
                DISTINCT('Team Scores by Week'[StadiumID]),
                    FILTER(
                        ALL('Team Scores by Week'),
                        ('Team Scores by Week'[HomeTeamID] = SELECTEDVALUE(Players[TeamID]) ||
                        'Team Scores by Week'[AwayTeamID] = SELECTEDVALUE(Players[TeamID])) &&
                        'Team Scores by Week'[Week] = SELECTEDVALUE('Master Schedule'[Week])
                    )
            )
    RETURN

    LOOKUPVALUE(Stadiums[Name],Stadiums[StadiumID], StadiumID)

Which results in again, just the home teams...
Capture2.PNG

 

However, when I modify the measure to retrieve just the stadium ID, it finds values for every game.  And those values correspond to the proper stadium.  Data types are the same between the two columns...just confused by this.

Capture.PNGCapture.PNG

Re: Populate table when multiple relationships exist between tables

Did a little research and just realized what's wrong: the relationship between Teams and Stadiums filters bi-directionally.  And because I have the Bears selected, Stadiums is filtered down to just Soldier Field.  So when the LOOKUPVALUE attempts to find stadium names, and with the Stadiums table filtered down to just Soldier Field, it's only going to return that value...

 

I found this which explains how to remove the cross-filtering on the Stadium table.  The final code of the measure is:

Home Stadium = 
    VAR
        StadiumID = 
            CALCULATE(
                DISTINCT('Team Scores by Week'[StadiumID]),
                    FILTER(
                        ALL('Team Scores by Week'),
                        ('Team Scores by Week'[HomeTeamID] = SELECTEDVALUE(Players[TeamID]) ||
                        'Team Scores by Week'[AwayTeamID] = SELECTEDVALUE(Players[TeamID])) &&
                        'Team Scores by Week'[Week] = SELECTEDVALUE('Master Schedule'[Week])
                    )
            )
    RETURN

    CALCULATE(
        CALCULATE(
            DISTINCT('Stadiums'[Name]),
            FILTER(
                ALL('Stadiums'),
                'Stadiums'[StadiumID] = StadiumID
            )
        ),
        CROSSFILTER(Teams[StadiumID],Stadiums[StadiumID],None)
    )

I was so close!  But now it works perfectly.  I didn't like my previous solution of duplicating the stadiums because I didn't want to duplicate data.