Forum Discussion
Return data from table 1 , using ID from table 2 that exists in table 1
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
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=czNHt7UXIe8thanks, 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
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.
3 Replies
- amitchandak
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=czNHt7UXIe8thanks, 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
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.