7# lookup value with find/search within same fact table.

[_google]

Hi

Reaching out for help to have lookup for below scenario in data table (which is within the data table). Not sure about how to manage in DAX ( PowerBI or PowerPivot)

my current excel formula Lookup(excel) =IFERROR(INDEX($B$3:$B$12,IF(ISNA(MATCH($C3,$D$3:$D$12,0)),MATCH("*"&$C3&"*",$D$3:$D$12,0),MATCH($C3,$D$3:$D$12,0))),"MV")

IFERROR(INDEX($Cntr Desc,IF(ISNA(MATCH($ID,Previous_ID,0)),MATCH("*"&$ID&"*",Previous_ID,0),MATCH($ID,Previous_ID,0))),"MV")

Basicually this help to identify ID is moveing or rotating in Cntrl centre.

Cntr Desc	ID	PREVIOUS_ID	Lookup ( Excel)
RePack	134892	359875	MV
Packing	748987S	773119	DePacking
Packing	278457	488777	DePacking
DePacking	762675	999841D	MV
DePacking	764309	278457D-748987S-326506D	MV
DePacking	748987D	748987	MV
DePacking	769547	543065	MV
PrePacking	769561	307417	MV
PrePacking	769939	769952	MV
PrePacking	773119	772727	Packing

[Floriankx]

Hello,

In PowerQuery you could load the Data twice.

First table: Cntr Desc, ID, PREVIOUS_ID

Second Table: Cntr Desc, ID

The second table you should filter dublicats.

Now you can 1. Continue in Power Query:

Append the tables matching PREVIOUS_ID and ID and expand Cntr Desc.

Afterwards you can replace Errors with 'MV'. This should lead to the expected result.

Or 2. go to PowerPivot

Load the tables to your data model. relate PREVIOUS_ID of first table with ID of second table.

A calculated column =RELATED(Table1[Cntr Desc]) should give you the correct result as well.

for Errors you can add ISERROR(RELATED(Table1[Cntr Desc]),'MV'

Best regards.