How to lookup the values of a column inside a tables' columns

[satlasg]

Hi, i am trying to generate a column based on values coming from a different table. 
Situation is as below:

Table with new columnLookup table
There is a link from "Location Code" to "Location" (one to many).
Now, based on "Location" and "Asset Type" the formula should check the Lookup table and return back a number (Performed in Excel with functions INDEX and MATCH).
How can this be achieved through DAX please?

Maybe i should build the Lookup table differently? Any ideas?

[Zubair_Muhammad]

Hi@satlasg

Just Unpivot your columns in LookUpTable (all columns other than Location Code)

Then you can use this calculated column formula in your Table with New Column

=Related(LookupTable[Values])

[satlasg]

Hi @Zubair_Muhammad,

tried the Unpivot but it couldnt possibly work, since it creates duplicates, so cannot build relation. 

Any idea what am i doing wrong?

[Zubair_Muhammad]

Hi,
You are right
Actually relationship is based on 2 columns after unpivoting I. E. ASSET TYPE and Location code
You can use lookup value dax function now to get the desired results

If you can share your file via googledrive or one drive, I will try to get you the result

[satlasg]

After UnPivot, i am left with those two unrelated tables: 

Table where column is neededUnpivoted Table

Cannot create a LookupValue funtion that will bring me the corrensponding Value in the first table.

[Zubair_Muhammad]

Hi @satlasg

Try this Calculated Column in your MainTable

(i.e. after unpivoting the LookUpTable)

Please change TableNames according to your case

Column =
CALCULATE (
    FIRSTNONBLANK ( UnpivotedTable[Value], 1 ),
    FILTER (
        UnpivotedTable,
        UnpivotedTable[Attribute] = MainTable[Asset type]
            && UnpivotedTable[Location Code] = MainTable[Location]
    )
)

[Zubair_Muhammad · Accepted solution]

@satlasg

Following formula should also give same results

Column =
LOOKUPVALUE (
    UnpivotedTable[Value],
    UnpivotedTable[Attribute], MainTable[Asset type],
    UnpivotedTable[Location Code], MainTable[Location]
)

[satlasg]

Great, thank you, both work, the second looks more straightforward. 

Just a note, in the first one, changing the second attribute of FIRSTNONBLANK to either 0, 2, 3, or even "" does not seem to have an effect.

[paulj1]

I am trying to do the same, any chance you can share an example file please.

Many thanks

Paul