cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mbp Frequent Visitor
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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Return value based on another tables filtered value

@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
Moderator v-yuezhe-msft
Moderator

Re: Return value based on another tables filtered value

@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.
Mbp Frequent Visitor
Frequent Visitor

Re: Return value based on another tables filtered value

Still seeking a solution.

Any takers?

Moderator v-yuezhe-msft
Moderator

Re: Return value based on another tables filtered value

@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.
Moderator v-yuezhe-msft
Moderator

Re: Return value based on another tables filtered value

@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

Mbp Frequent Visitor
Frequent Visitor

Re: Return value based on another tables filtered value

@v-yuezhe-msft

 

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

 

 

 

 

EDIT: Nevermind

 

 

Thank you again.

- Matt 

Mbp Frequent Visitor
Frequent Visitor

Re: Return value based on another tables filtered value

EDIT: Fixed

Moderator v-yuezhe-msft
Moderator

Re: Return value based on another tables filtered value

@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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors