question

Lz-3068 avatar image
0 Votes"
Lz-3068 asked erinding-msft commented

Excel PowerPivot LOOKUPVALUE <alternateResult> argument

Hi

I run Excel 365 v2104 b13929.20386 where it appears DAX LOOKUPVALUE function does not accept the <alternateResult> argument

PBI Desktop

 =LOOKUPVALUE('Product'[Product], 'Product'[ProductKey], 123, 0) // Works no problem

Excel PowerPivot

 =LOOKUPVALUE('Product'[Product], 'Product'[ProductKey], 123, 0) // Not accepted

Error message
The number of arguments is invalid. Function LOOKUPVALUE must have a value for each specified column reference

Function doc.
The date (10/14/2020) seems to suggest either a doc. and/or a function revision/change by end of 2020
Does anyone have any info. regarding this and knows if it's currently expected that Excel PP doesn't support the <alternateResult> arg.?

Possible workaroud

 VAR tryLookup = LOOKUPVALUE('Product'[Product], 'Product'[ProductKey], 123)
 RETURN
 IF( ISBLANK(tryLookup), 0, tryLookup)

Thanks


office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
1 Vote"
Lz-3068 answered erinding-msft commented

Apparently the <alternateResult> optional parameter was made avail. as part of the Power BI March 2019 release


Thanks much @erinding-msft, it's really appreciated & no worries re. the delay, there's no emergency :)

Given your test with Excel 2019 Pro Plus (more functionalities than a Home/Family edition), it seems reasonable to conclude that if the new version of LOOKUPVALUE is still not available 2 years after its avail. in PowerBI, it will likely never be available in Excel (or in yearss from now)

Closing this thread...


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have a nice day~ :)

0 Votes 0 ·
erinding-msft avatar image
1 Vote"
erinding-msft answered erinding-msft commented

Hi @Lz-3068

Per my test with a simple sample, I reproduced this issue.
99367-image.png

It's weird. As shown in the following screenshot, syntax of LOOKUPVALUE function does not contains <alternateResult> argument which is different from that in the 10/14/2020 document.
99432-image.png

By the way, thanks for your possible workaround.

To help improve this function, you may submit a feedback to Microsoft.

Thanks for your understanding.


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


image.png (38.9 KiB)
image.png (14.4 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @erinding-msft. I appreciate you spent time to check it on your side (with which version?)

On the other hand my question remains: Does anyone knows if it's currently expected that Excel PP doesn't support the <alternateResult> arg.?

0 Votes 0 ·

Hi @erinding-msft. Could you please let me know which version (365, 2016, 2019) you used when you checked and which "edition" (Home, Pro, Business...)? Thanks much in advance

0 Votes 0 ·

Hi @Lz-3068
Sorry for the delay.
I used Microsoft 365 (the same version as yours), Office Professional Plus 2019 (v1808 10374.20040) and Office Professional Plus 2016 (16.0.4266.1001) to test.
All are the same result.

0 Votes 0 ·