Looking up values from two columns in a different table

[An0n]

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!

[SamInogic]

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 crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[An0n]

Thanks for your suggestion. In the actual data there are 100K rows so multiple rows with value amounts with different split of column attributes.