Latest Enterprise DNA Initiatives

Underrated Lookupvalue

I was not aware of the lookupvalue function until lately. It’s extremely useful to grab columns from unrelated tables.

My question; why doesn’t this function get any love in tutorials? I don’t believe I’ve seen it in the Mastering Dax module.

Is there a hidden issue (besides potential performance) that I’m missing or is it just that I was blind to this amazingly underrated function until now?

Chris

Great! Doesn’t answer the question though!

Sometimes it’s nice to have a pleasant conversation with like minded people Keith.

But thanks!

@cms418 ,

Here’s my $0.02. It’s good as syntax sugar for specific cases, but it’s basically a FILTER construct in Groucho glasses. And writing it out the long way let’s you add more than one matching condition. I use it, but if it disappeared tomorrow, I wouldn’t miss it.

Here’s an example from a recent forum response where I did a lookup with 3 matching conditions. If the solution required only one, I could have used LOOKUPVALUE as a substitute.

Rate Lookup = 

VAR SelStaff = SELECTEDVALUE( Hours_Worked[Staff ID] )
VAR SelCat = SELECTEDVALUE( Hours_Worked[Category] )
VAR SelDate = SELECTEDVALUE( Hours_Worked[WeekEndingFriday] )

VAR FindRow =
FILTER(
    ALL( Rate_Data ),
    Rate_Data[Staff_ID] = SelStaff &&
    Rate_Data[Category] = SelCat &&
    Rate_Data[rate_activefrom] <= SelDate &&
    Rate_Data[rate_expired_on] >= SelDate
)

VAR Result =
MAXX( FindRow, Rate_Data[Hourly Rate] )

RETURN Result

I think of it a lot like 90% of the time intelligence functions - helpful shorthand conveniences, but basically unnecessary.

  • Brian
2 Likes

One aspect that has kept me from using LOOKUPVALUE more often is this line from the “Remarks” section of the Microsoft page on it:

If there is a relationship between the result and search tables, in most cases, using RELATED function instead of LOOKUPVALUE is more efficient and provides better performance.

Another sticking point for me is that most of the examples I see for LOOKUPVALUE involve calculated columns - which I rarely have a good use case for.

2 Likes

I used LookupValue extensively in one PBIX I wrote with a lot of unrelated tables.
It’s use was mainly in calculated columns rather than any dynamic dax and so had no real impact on performance post refresh.

But as @Heather says RELATED would be more efficient in measures.

Pete

1 Like

Hi @cms418, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.