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.
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.
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!
Solved! Go to Solution.
@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
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.
The problem with this approach is that the table won't recalculate whenever selections change.
Forgot to include results of the creating the table...
I don't believe there is another way to interactively calculate a table and then use those results to populate a table...
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...
@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,
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...
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...
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...
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.
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.