I got an interesting question last night in preparation for the world cup. How can I get a PivotTable where I can see the results where the team selected in a slicer is either the Home or the Away team. Because we need to do show games where the Home or the Away team are selected here we cannot use traditional relationships.
This is the table of games I have (yes its fake, I made it myself before you wonder if these games are actually played):
As you might see the data we have above is also from the past, I see teams like “west germany”, I want to group the old names with the new names. To do that I created this table:
So I created the following worksheet, I put Home and Away teams on rows. Country from the Team table and Year from the games table as slicers
Now I need to use a DAX expression to show the goals where either the home or away country is equal to the Country selected on the slicer. In order for me to do this I need to be able to compare the two columns that are equal, so I cannot use the team column as that would not compare “West germany” with “Germany”. I do this by adding the Country as a calc colum to the Teams table by using Lookupvalue:
=LOOKUPVALUE(Country[Country], Country[Team], Games[HomeTeam])
This will add the value for Country[Country] to the column where the Country[Team] column is equal to Games[HomeTeam] for the row we are in:
Now I can use a DAX expression to get the Homegoals where the home or away team is from the selection. The below expression gets the SUM of Games[Homegoals] for all the rows in the Games table where the Games[HomeCountry] is the VALUE of the Country[Country] selection OR Games[AwayCountry] is the VALUES of the Country[Country] selection. Make sure to note we use the “Games” table as first argument, that will make sure all the filters, like year, are still applied:
=IF (
HASONEVALUE ( Country[Country] ),
SUMX (
FILTER (
Games,
Games[HomeCountry] = VALUES ( Country[Country] )
|| Games[AwayCountry] = VALUES ( Country[Country] )
),
Games[Homegoals]
),
BLANK ()
)
This gets us the results we want:
Talking to some of my colleagues we decided that there had to be a more optimal way. Taylor my colleague came up with the following, this doesn’t need any of the calc columns. It will calculate the sum of HomeGoals for each row of the filtered Games table where the values of the Games[Hometeam] row CONTAINS the selected (by the slicer) Country[Team] column or the Games[Awayteam] row CONTAINS the selected (by the slicer) Country[Team].
=CALCULATE (
SUM(Games[HomeGoals]),
FILTER (
Games,
CONTAINS ( Country, [Team], Games[Hometeam] )
|| CONTAINS ( Country, [Team], Games[Awayteam] )
)
)
This is a much more elegant solution and also supports selecting multiple countries. This again goes to show that there is no single way of solving any problem using DAX, there is always room to optimize 