Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |