Lookup value matching more than one (Index Match)

[Saxon202202]

I have two tables: "Data" and "Report."
The "Data" table consists of columns such as type, id, code, and country.
Meanwhile, the "Report" table includes columns named Item, type, code, and country.
The connection between these tables is established through the type, id, and code columns.
My objective is to extract the id from the "Data" table and incorporate it into the "Report" table, utilizing the criteria of matching type, code, and country.

Report Table:

Item	Type	Code	Country	Desired Result
123	AZ1	0070		077
124	AZ1	1000		077
125	AZ1	0070		077
126	AZ1	1000		077
127	A07	0600		CHE
128	A07	0930		CHE
129	A07	0600		CHE
130	A07	0930		CHE
131	A08	0840	UK	GT
132	A08	0840	US	LUK
133	A08	0840	EUR	RR
134	A08	0840
135	AA1	0260	UK	RR
136	AA1		US	CHE
137	AA1		EUR	GT
138	AA1		BAL	LUK
139	AA1		PAK	SL1
140	AA1		IND	SL2
141	AA1		IT	SL3
142	AA1		CAN	SL4
143	AA1		AUS	SL5

Data:

Type	Id	Code	Country
A02	LPG	0140
AZ1	077	0070
AZ1	077	1000
A01	RBI	0160
A06	A01	0490
A06	A02	0990
A07	CHE	0600
A07	CHE	0930
A08	SRH	0840
A08	DEL	1050
A08	MI	0840
A08	GT	0840	UK
A08	LUK	0840	US
A08	RR	0840	EUR
A08	RCB	0610
A08	KKR	0200
A08	XPHJ
AV1	X01	1160
AA1	RR	0260	UK
AA1	CHE		US
AA1	GT		EUR
AA1	LUK		BAL
AA1	SL1		PAK
AA1	SL2		IND
AA1	SL3		IT
AA1	SL4		CAN
AA1	SL5		AUS

In Excel I am applying the following formula to get the result:
=IFERROR(INDEX(DATA!$B$3:$B$29,MATCH($B3&$C3&$D3,DATA!$A$3:$A$29&DATA!$C$3:$C$29&DATA!$D$3:$D$29,0)),"")

I am using the below mentioned DAX but it's not working

Desired Result1 =

LOOKUPVALUE(DATA[Id],DATA[Code],REPORT[Code],DATA[Country],REPORT[Country],DATA[Type],REPORT[Type])

[Ashish_Mathur]

Hi,

Write this calculated column formula in the Report table

Column = CALCULATE(MAX(Data[Id]),FILTER(Data,Data[Type]=EARLIER(Report[Type])&&Data[Code]=EARLIER(Report[Code])&&Data[Country]=EARLIER(Report[Country])))

Hope this helps.

[lbendlin]

Does it need to be DAX or can this be in Power Query?

[Saxon202202]

@lbendlin, Thanks for your reply. If possible to provide the solution both ways(DAX new calculate column and Power query) it will help to understand the Power BI. 

[lbendlin · Accepted solution]

Table Report:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDLDoMgEAXQXzGsXQwPQZfUmrYpMY2PTY3//xsyA8WgqQtHkuPNHVkWxoVkJbNf7ieAAf8q8GgMW0tklZgDnLm6TuvrtEEGnKATt88ucr1zI8/cXKYp8D8tOTFWQK2Q57cfjymyOPHoh/MfBZdH7+bBz2GIro5e0BOQbs3SrQmdulNWJy5+xfveJsPQmrauM7xZl+3cZPqxWDo6HpSW3PXV30lFVJ7rRCgjigxb25OqqDJTS/8zuoqt6wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Code = _t, Country = _t, #"Desired Result" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Type", "Code", "Country"}, Data, {"Type", "Code", "Country"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"Id"}, {"Id"})
in
    #"Expanded Data"

In DAX you would similarly use a compaosite key

The problem is that it is not unique - A080840 appears twice. That introduces ambiguity.