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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AyubSherif
Frequent Visitor

Performance of Lookupvalue and Alternatives

I have a Master Data table and a Key table, and I need to look up 13 columns from the master data in the key table.

Master Data:

ProjectIDValue1Value2Value3
1ACA
2BBC

Key Table:

ValueScore
A100
B90
C80

 

I have the two tables separate and created a measure for each column I need to look up:

 

Value1_Lookup = LOOKUPVALUE('Key Table'[Score],'Key Table'[Value],SELECTEDVALUE('Master Data'[value 1]))

 

But I am concerned about performance. Is there a better way to do this? I tried the same measure with a Switch function instead of  lookupvalue, but the performance is not much better. Does creating calculated columns into my Master Data a better option here?

Notes, my key table only has 5 values

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@AyubSherif Perhaps do a Merge query step in Power Query?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

TomasAndersson
Solution Sage
Solution Sage

Hi!

You can read more about LOOKUPVALUE() and some performance considerations with alternatives here: https://dax.guide/lookupvalue/

Calculated columns are quick as they are calculated on beforehand, when the model is loaded. For the same reason however the model also becomes bigger with calculated columns, so it depends on where your main concerns are. 

View solution in original post

2 REPLIES 2
TomasAndersson
Solution Sage
Solution Sage

Hi!

You can read more about LOOKUPVALUE() and some performance considerations with alternatives here: https://dax.guide/lookupvalue/

Calculated columns are quick as they are calculated on beforehand, when the model is loaded. For the same reason however the model also becomes bigger with calculated columns, so it depends on where your main concerns are. 

Greg_Deckler
Super User
Super User

@AyubSherif Perhaps do a Merge query step in Power Query?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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