2# lookup value in Power pivot with find/search within same fact table.

[_google]

Hi

Reaching out to this forum ( looks is great on power pivot ) 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 (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")

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

Basically this help to identify ID is moving or rotating in Cntrl center.

Cntr Desc IDPREVIOUS_ID Lookup ( Excel)
RePack 134892359875 MV
Packing 748987S773119 DePacking
Packing 278457488777 DePacking
DePacking 762675999841D MV
DePacking 764309278457D-748987S-326506D MV
DePacking 748987D748987 MV
DePacking 769547543065 MV
PrePacking 769561307417 MV
PrePacking 769939769952 MV
PrePacking 773119772727 Packing

 

[GraH - Guido]

Hi,
There is no real "lookup" like that in DAX to my knowledge. The power comes from the relationship between tables based on a unique key (of the dimension/lookup table) matching a column in the fact table. You then can use, if necessary, the functions RELATED (from fact table to dimension table) or RELATEDTABLE (from dimension table to fact table). I find it hard to imagine how either one would help you out, since you work in the same table.
I'm guessing a combination of DAX functions (earlier, values, filter, calculate,...) might do what you need. But filtering directly within the fact table might be a bad thing for overall performance.
It sounds better to first prepare the data over in Power Query, where lookup is like ridiculously easy and then send that new table to the data model.
Perhaps if you take the time to upload a small sample file and a reduced data model, some of our DAX experts may find something that's working.
I actually believe I'm going to learn something myself from following this thread.

 

[Chihiro]

Upload sample workbook with your raw data and expected output. Without it, it's near impossible to give you solution.

DAX equivalent of VLOOKUP is LOOKUPVALUE().
https://msdn.microsoft.com/en-us/query-bi/dax/lookupvalue-function-dax
https://blog.westmonroepartners.com/favorite-quick-easy-dax-lookup-values/

 

[GraH - Guido]

Nice, that one is not present in v1 of PP, which happens to be the only version I know a bit about. It got introduced in v2 I read. Makes me feel like an old man that can't keep up any more with the newer stuff.

 

[_google]

Hi Chihiro and GraH

Have uploaded the sample raw data file with my current excel formula output. Have not attempted to create data model or load in power pivot as believe it easy work new file the do correction.

 

[Chihiro]

Hmm, this is difficult one. And DAX/PQ isn't well suited to this sort of operation.

Let me think on it a bit.

 

[GraH - Guido]

Chihiro, if we could turn the previous_ID into a list, it could be extended to rows. Making it possible to match ID with Previous_ID.
Just tried during my break, but couldn't figure it out right away.

 

[Chihiro]

Ok, here's how to do it.

M
1. In PQ, add custom column concatenating [GRP] & [Wrk]
Edit: Forgot to mention, that you should have all columns as Text type.
2. Replicate query and remove all but [ID], [Previous_ID], [Custom] (called it Table1)
3. Load both table to data model and go to PowerPivot Wizard.

DAX
4. In Table1 add following calculated column.
[pMatch]

= FIRSTNONBLANK(FILTER(VALUES(fTable[ID]),SEARCH(fTable[ID],Table1[PREVIOUS_ID],1,0)),1)

5. In fTable add following calculated column.
[Lookup]

=SWITCH(TRUE(),ISBLANK(LOOKUPVALUE(Table1[Custom],Table1[pMatch],fTable[ID])),"mv",LOOKUPVALUE(Table1[Custom],Table1[pMatch],fTable[ID]))

6. Add Key column.
[Key]

=IF(fTable[Lookup]="mv",1,2)

See attached.

 

[GraH - Guido]

Hi,
I found a working alternative via PQ.
I managed to create the multiple lines from the Previous_IDs. Then merged the table on itself, extracted the required columns and added some custom columns.

• First Qry to prepare the data
• Second data to create the list and extract the list
• Third query as merge to do the "lookup"

 

[_google]

Hi Chihiro,

Many thanks again, its perfect... will try to understand the DAX firstnonblank on Microsoft site.

Hi GraH
thank, this help to but need to spend some time of learning power query split and merge

 

[GraH - Guido]

Hi, _google,

Cleaned up the code a bit, simplified some applied steps and renamed the applied steps properly. Content has not changed, but it should be more readable for you.
Enjoy the learning of PQ.