LOOKUPVALUE doesn't seem to work with Alternate Results

[RandomJoe]

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?

[Anonymous]

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]

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

[RandomJoe]

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