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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
satlasg
Helper I
Helper I

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

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


Table with new columnTable with new columnLookup tableLookup 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?

1 ACCEPTED SOLUTION

@satlasg

 

Following formula should also give same results

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals

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?

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

Regards
Zubair

Please try my custom visuals

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

 

Table where  column is neededTable where column is neededUnpivoted TableUnpivoted Table

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

 

 

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

 


Regards
Zubair

Please try my custom visuals

@satlasg

 

Following formula should also give same results

 

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

Regards
Zubair

Please try my custom visuals

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.

 

 

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

 

Many thanks

 

Paul

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.