cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors