Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm trying to create two columns which are filtered based on a value from another table.
Here is my model:
In the Dim_Country table I would like to create two columns; HomeTeam and AwayTeam. Both of these values come from the Dim_Country tables Country field. The MatchRole field in the Link_CountryMatch table is what determines whether a country performs as a home team (MatchRole = 1) or an away team (MatchRole = 0) for a given match.
The SQL query looks like this:
SELECT m.MatchID, d.MatchDate, t.Tournament, MAX(h.Country) AS HomeCountry, MAX(a.Country)
AS AwayCountry, m.HomeScore, m.AwayScore, m.Scoreline FROM dbo.Fact_Match m LEFT JOIN Dim_Tournament t ON m.Tournament_FK = t.TournamentID LEFT JOIN Dim_Date d ON m.Date_FK = d.DateID LEFT JOIN Link_CountryMatch l ON m.MatchID = l.Match_FK LEFT JOIN Dim_Country h ON l.Country_FK = h.CountryID AND l.MatchRole = 1 LEFT JOIN Dim_Country a ON l.Country_FK = a.CountryID AND l.MatchRole = 0 GROUP BY m.MatchID, d.MatchDate, t.Tournament, m.HomeScore, m.AwayScore, m.Scoreline ORDER BY MatchDate DESC, MatchID
And produces these results (sample):
MatchID MatchDate Tournament HomeCountry AwayCountry HomeScore AwayScore Scoreline
473843 2017-11-15 00:00:00.000 FIFA World Cup qualification Australia Honduras 3 1 2
473844 2017-11-15 00:00:00.000 FIFA World Cup qualification Peru New Zealand 2 0 2
473811 2017-11-14 00:00:00.000 FIFA World Cup qualification Burkina Faso Cape Verde 4 0 4
473812 2017-11-14 00:00:00.000 FIFA World Cup qualification Senegal South Africa 2 1 1
473813 2017-11-14 00:00:00.000 AFC Asian Cup qualification Macau Kyrgyzstan 3 4 1
I've tried playing around with FILTER and RELATED/RELATEDTABLE functions but haven't been able to replicate these results.
Help would be much appreciated.
Thanks
Solved! Go to Solution.
@Mbp,
Create the following measures in your Dim_Country table.
AwayCounty = CALCULATE(VALUES(Link_CountryMatch[RelatedCountry]),FILTER(Dim_Country,CALCULATE(COUNTROWS(FILTER(Link_CountryMatch,Link_CountryMatch[MatchRole]=FALSE()))>0)))
HomeCountry = CALCULATE(VALUES(Link_CountryMatch[RelatedCountry]),FILTER(Dim_Country,CALCULATE(COUNTROWS(FILTER(Link_CountryMatch,Link_CountryMatch[MatchRole]=TRUE()))>0)))
Regards,
Lydia
Still seeking a solution.
Any takers?
@Mbp,
Create the following measures in your Dim_Country table.
AwayCounty = CALCULATE(VALUES(Link_CountryMatch[RelatedCountry]),FILTER(Dim_Country,CALCULATE(COUNTROWS(FILTER(Link_CountryMatch,Link_CountryMatch[MatchRole]=FALSE()))>0)))
HomeCountry = CALCULATE(VALUES(Link_CountryMatch[RelatedCountry]),FILTER(Dim_Country,CALCULATE(COUNTROWS(FILTER(Link_CountryMatch,Link_CountryMatch[MatchRole]=TRUE()))>0)))
Regards,
Lydia
EDIT: Fixed
@Mbp,
Please open a new thread about the above issue.
Regards,
Lydia
@Mbp,
I have sent you a Private Message.
Regards,
Lydia
@Mbp,
Would you please share the sample data of all tables or the PBIX file to me via Private Message?
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |