Forum Discussion
Looking up values from two columns in a different table
Dear community,
In my PBI model I have two tables, with the below random sample data. I want to link Table 1 and Table 2 by looking up the CONTRACT in Table 2 from either ID1 or ID2 column and match it to the ID column in Table 1 to then show a combination of the tables in the Visualization view (in a table). I tried with creating relationships but it does not work because you can't have two many-to-many relationships active at the same time.
Table 1:
| ID | Other column | Amount ID1 | Amount ID2 |
| FAR17942 | xyz | 9 | 0 |
| 3406132 | abc | 0 | 170 |
| 1122208 | def | 0 | 2000 |
| 112202 | jkl | 340 | 0 |
| 3406115 | ghi | 80 | 0 |
| FAR17947 | mno | 0 | 15 |
Table 2:
| CONTRACT | ID1 | ID2 |
| DEAL05 | FAR17942 | FAR17947 |
| DEAL12 | 3406115 | 3406132 |
| DEAL19 | 112202 | 1122208 |
Desired Table in Visualization view:
| CONTRACT | ID1 | ID2 | Amount ID1 | Amount ID2 |
| DEAL05 | FAR17942 | FAR17947 | 9 | 15 |
| DEAL12 | 3406115 | 3406132 | 80 | 170 |
| DEAL19 | 112202 | 1122208 | 340 | 2000 |
Thanks in advance!
2 Replies
- SamInogic
Super User
Hi An0n ,
You can create two “Amount ID1” and “Amount ID2” columns within “Table 2” table with below DAX expression for expected output.
Amount ID1 = LOOKUPVALUE('Table 1'[Amount ID1], 'Table 1'[ID], 'Table 2'[ID1])
Amount ID2 = LOOKUPVALUE('Table 1'[Amount ID2], 'Table 1'[ID], 'Table 2'[ID2])
Please refer to the below screenshot for the output.
If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.
Thanks!Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Thanks for your suggestion. In the actual data there are 100K rows so multiple rows with value amounts with different split of column attributes.
