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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
pjm1181
New Member

Return data from table 1 , using ID from table 2 that exists in table 1

Picture1.png


Apologies if this has been answered, I’ve tried searching, and part of the issue is that I don’t know how to describe my issue! Hence the poor title

 

I have 2 tables, simplified above. Table 1 is a data table, where the main bulk of risk data is housed. Each risk ID has a single row of data.

Table 2 is also a data table, but this just brings out the relationship between the risks.

 

I have a relationship between reports on the Risk ID, but my struggle is to populate the owner of the risk in table 2 using data from table 1.

 

I could use DAX and LOOKUP, and I have as a temp measure, but long term I want to use the data from table 1 against the ID from table 2. For context, this is a risk hierarchy, the level 0 risks have level 1 risks linked to them. I have an individual dashboard for each level 0 risk as there are not many. This dashboard shows the linked risks from table 2. I want to be able to drill through from the level 0 and produce a sub dashboard for the level 1 risk, bringing back the owner plus a whole load more data

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@pjm1181 , a new column in table two

 

Maxx(filter(Table1, Table1[Risk ID] = Table2[Linked Risk ID] ), Table1[Owner])

 

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

thanks, i somewhat managed to solve it by making a bridging table, witout any DAX code as that was my original intnetion. but if anybody has better ideas, more than happy to try

View solution in original post

v-zhengdxu-msft
Community Support
Community Support

Hi  
Thanks for @amitchandak !
Here I would like to add your method, let me give you a detailed example:

I create 2 relationship between the two table:

 

Then add 2 calculate column2 in table2:

 Column1 = RELATED(Table1[Owner])Column2 = CALCULATE(SELECTEDVALUE(Table1[Owner]),USERELATIONSHIP(Table1[Risk ID],Table2[Linked Risk ID]),REMOVEFILTERS(Table1)) 

The result is as follow:

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vzhengdxumsft_2-1709272154116.png

 

 

 

vzhengdxumsft_0-1709272066574.png

@pjm1181

View solution in original post

3 REPLIES 3
v-zhengdxu-msft
Community Support
Community Support

Hi  
Thanks for @amitchandak !
Here I would like to add your method, let me give you a detailed example:

I create 2 relationship between the two table:

 

Then add 2 calculate column2 in table2:

 Column1 = RELATED(Table1[Owner])Column2 = CALCULATE(SELECTEDVALUE(Table1[Owner]),USERELATIONSHIP(Table1[Risk ID],Table2[Linked Risk ID]),REMOVEFILTERS(Table1)) 

The result is as follow:

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vzhengdxumsft_2-1709272154116.png

 

 

 

vzhengdxumsft_0-1709272066574.png

@pjm1181

amitchandak
Super User
Super User

@pjm1181 , a new column in table two

 

Maxx(filter(Table1, Table1[Risk ID] = Table2[Linked Risk ID] ), Table1[Owner])

 

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

thanks, i somewhat managed to solve it by making a bridging table, witout any DAX code as that was my original intnetion. but if anybody has better ideas, more than happy to try

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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