cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamieLee
Frequent Visitor

Return value from other table with condition

Hi everyone, I would appreciate some help on this:

 

I have two tables that are linked through an OrderID. Let’s call the tables TableA and TableB.

TableA has unique OrderIDs. In TableB an OrderID can appear multiple times.

 

In TableA I want to add a calculated column with the name ‘Country’.

I can find the value for this new column in TableB[Keyword]. However TableB does not have unique OrderIDs and needs to be filtered on TableB[KeywordGroup] first. Only if the value in TableB[KeywordGroup] is “Country”, I want to return the value from TableB[Keyword].

 

So what I’m trying to achieve is this:

In the new column TableA[Country] I would like to return the value from TableB[Keyword] if TableB[KeywordGroup] = Country.

It’s probably very logical, but can’t find the right way of getting this done.

 

Hope you can help.

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @JamieLee

In table B, create a new table

Table = SUMMARIZE(FILTER(ALL(tableB),[KeywordGroup]="country"),[OrderID],[Keyword])

Then edit relationship between Table A and the new table

13.png

 

In table A, create a calculated column

Column = RELATED('Table'[Keyword])

14.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @JamieLee

In table B, create a new table

Table = SUMMARIZE(FILTER(ALL(tableB),[KeywordGroup]="country"),[OrderID],[Keyword])

Then edit relationship between Table A and the new table

13.png

 

In table A, create a calculated column

Column = RELATED('Table'[Keyword])

14.png

 

Best Regards

Maggie

View solution in original post

This works. Thanks Maggie!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors