Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Saxon202202
Helper III
Helper III

Lookup value matching more than one (Index Match)

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:

ItemTypeCodeCountryDesired Result
123AZ10070 077
124AZ11000 077
125AZ10070 077
126AZ11000 077
127A070600 CHE
128A070930 CHE
129A070600 CHE
130A070930 CHE
131A080840UKGT
132A080840USLUK
133A080840EURRR
134A080840  
135AA10260UKRR
136AA1 USCHE
137AA1 EURGT
138AA1 BALLUK
139AA1 PAKSL1
140AA1 INDSL2
141AA1 ITSL3
142AA1 CANSL4
143AA1 AUSSL5

 

Data:

TypeIdCodeCountry
A02LPG0140 
AZ10770070 
AZ10771000 
A01RBI0160 
A06A010490 
A06A020990 
A07CHE0600 
A07CHE0930 
A08SRH0840 
A08DEL1050 
A08MI0840 
A08GT0840UK
A08LUK0840US
A08RR0840EUR
A08RCB0610 
A08KKR0200 
A08XPHJ  
AV1X011160 
AA1RR0260UK
AA1CHE US
AA1GT EUR
AA1LUK BAL
AA1SL1 PAK
AA1SL2 IND
AA1SL3 IT
AA1SL4 CAN
AA1SL5 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)),"")

Saxon202202_0-1703866268771.png


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])

 

1 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

lbendlin_0-1704063051168.png

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

 

 

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1704066011521.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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

@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. 

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

lbendlin_0-1704063051168.png

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

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.