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
RandomJoe
Frequent Visitor

LOOKUPVALUE doesn't seem to work with Alternate Results

I have tried a couple different ways to use LOOKUPVALUE to return an AlternateResult as described in the documentation: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

 

This works: Level2 = LOOKUPVALUE( [Department Name], [Department ID], PATHITEM ( [Path], 2, TEXT ))

 

But in some cases zero results are returned and so the function I believe is following the described behavior "the function returns BLANK() when result_columnName is filtered down to zero value or an error when more than one distinct value."

 

This doesn't work: Level3 = LOOKUPVALUE( [Department Name], [Department ID], PATHITEM ( [Path], 3, TEXT ), "Zero or Multiple values")

 

It returns an error that says the the number of arguments is invalid. So, what am I doing wrong?

3 REPLIES 3
Anonymous
Not applicable

First, please do follow Best Practices and precede column names with their table's name. Measures should not be preceded. This is extremely important to be able to read code and not confuse measures with column names. Why is this important? Because measures do context transition and one should be able to immediately spot it.

 

Secondly, I can't tell you what's wrong because I don't have a context. Please post a link to the file. I suspect something's not right with PATHITEM in this context. Please try to calculate this into a variable and then use the variable in LOOKUPVALUE.

 

Thirdly, please read this in full to see how you can replace this function with some better performing code:

 

https://dax.guide/lookupvalue/

 

Best
D

MSTNGR
New Member

I've had the same problem.  Did you ever figure it out?

No. I never got it to work. Let me know if you figure it out. 🙂

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.

Top Solution Authors