Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mbp
Frequent Visitor

Return value based on another tables filtered value

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

1 ACCEPTED 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)))


1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Mbp
Frequent Visitor

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)))


1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

EDIT: Fixed

@Mbp,

Please open a new thread about the above issue.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

You're a champion Lydia, thank you so much.

 

 

 

 

EDIT: Nevermind

 

 

Thank you again.

- Matt 

@Mbp,

I have sent you a Private Message.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yuezhe-msft
Employee
Employee

@Mbp,

Would you please share the sample data of all tables or the PBIX file to me via Private Message? 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.