Hello EDNA Forum
I’m looking for the Dax equivalent for a Vlookup in Excel. Per the PBIX attached I have provided two sources:
In the all customer table I would like to flag the customers in the customer match table.
In Excel this task is straightforward, how can I do this in Power BI using Dax.
Thank you in advance for your time.
Regards,
Supplier Match Example.pbix (106.4 KB)
Hello @ambidextrousmentally,
Thank You for posting your query onto the Forum.
Well there are several ways to achieve this results. That is, by way of creating measures as well as by creating calculated columns.
Firstly, I created a “One-To-Many Relationship” between the two tables flowing from “UPBI Supplier Match” to “UPBI All Customers”. Below is the screenshot provided for the reference -
Now, let’s see how you can do this with the help of “Calculated Columns”. I’ve created three calculated columns just to show how you can achieve the results and which result is more efficient than the other.
1. Simple VLOOKUP function to find out “Matching Customers” -
VLOOKUP Column =
LOOKUPVALUE(
'UPBI Supplier Match'[Customer Match] ,
'UPBI Supplier Match'[Customer Match] ,
'UPBI All Customers'[Customer Name] )
2. Based on previous step/formula creating another column to find out “Matched - Unmatched Customers” -
Matching Column 1 =
SWITCH( TRUE() ,
'UPBI All Customers'[VLOOKUP Column] = BLANK() , "Unmatched Customers" ,
"Matched Customers" )
3. Consolidated use of VLOOKUP Function to find out “Matched - Unmatched Customers” through flagging -
Matching Column 2 =
IF(
'UPBI All Customers'[Customer Name] =
LOOKUPVALUE(
'UPBI Supplier Match'[Customer Match] ,
'UPBI Supplier Match'[Customer Match] ,
'UPBI All Customers'[Customer Name] ) ,
UNICHAR( 127937 ) ,
UNICHAR( 128681 ) )
4. Result in the form of “Measure” -
VLOOKUP Measure =
IF(
SELECTEDVALUE( 'UPBI All Customers'[Customer Name] ) =
LOOKUPVALUE(
'UPBI Supplier Match'[Customer Match] ,
'UPBI Supplier Match'[Customer Match] ,
SELECTEDVALUE( 'UPBI All Customers'[Customer Name] ) ) ,
UNICHAR( 127937 ) ,
UNICHAR( 128681 ) )
So now, since we’ve seen all the available options. I would firstly, not recommed to go with the measures options because you’ll not be able to filter out the flags. And options 2 is dependent on option 1. So by far, option 3 is the feasible and viable option because you can also filter out the flags. Below is the screenshot provided of the final results -
I’m also attaching the working of the PBIX file for the reference. Also providing a link of the video of the VLOOKUP function as well as also providing a link of the post which @Greg had created pertaining to the DAX “UNICHAR()” function from where you can also download the working files for the reference.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Thanks and Warm Regards,
Harsh
Supplier Match Example - Harsh.pbix (114.9 KB)
Hi @ambidextrousmentally , 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 the thread as solved. Thanks!
Hi @ambidextrousmentally, we’ve noticed that no response has been received from you since the 19th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Hi @ambidextrousmentally, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
