Forum Discussion
Default values with lookup function
I have two table 1. Dynamic(will update realtime) 2) static reference file and used lookupvalue function to retrive value of one column from static rerence file to dynamic(new column). But, is there any option i can use as default value incase there is no matching. Also i need to derive default value of new column in dynamic table using existing coloumn of dynamic column.
Hi Anonymous ，
According to your description,I created 2 sample tables as below:
Then create a calculated column as below:
Column = LOOKUPVALUE(Table1[Value],'Table1'[Category],'Table2'[Category],Table2[Value])And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
5 Replies
Anonymous - Yes, the last parameter of LOOKUPVALUE is the alternate result to be used if more than one value is found or no values are found:
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
Thanks for reply. but my requirement bit different here
Some ex
Table-a col-1 col-2 Table-b col-1 ,col-3
currently I used table-a(col2) = lookupvalues(table-b(col3), table-b(col-1),table-a(col-1)) whis return value if table-a[col1] = table-b(col1), if not match i need pouplate table-a(col-2) = table-a(col-1). Hope its clear
Hi,
Do any of these work?
=lookupvalues(table-b(col3), table-b(col-1),table-a(col-1),table-a(col-1))
or
=if(isblank(lookupvalues(table-b(col3), table-b(col-1),table-a(col-1))),table-a(col-1),lookupvalues(table-b(col3), table-b(col-1),table-a(col-1)))
Hope this helps.
Anonymous
The LOOKUPVALUE function has the last paramter for the alternative result
[Region] = LOOKUPVALUE(Employee[Region], Employee[Email], USERNAME(), BLANK())
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
Hi Anonymous ，
According to your description,I created 2 sample tables as below:
Then create a calculated column as below:
Column = LOOKUPVALUE(Table1[Value],'Table1'[Category],'Table2'[Category],Table2[Value])And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
