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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.