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

[pjm1181]

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

[amitchandak · Accepted solution]

@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

[pjm1181 · Accepted solution]

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

[v-zhengdxu-msft · Accepted solution]

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.

@pjm1181

[amitchandak · Accepted solution]

@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

[pjm1181 · Accepted solution]

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