Dax Excel V lookup Equivalent to record a fine one record as a starter

DAX DAX Calculations
2

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 -

Relationship Status

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] )

Result 1
Result 1465×636 9.26 KB

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" )

Result of Calculated Column - 1 in UPBI All Customers Table
Result of Calculated Column - 1 in UPBI All Customers Table546×761 11.5 KB

Result 2
Result 2696×628 12.8 KB

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 ) )

Result of Calculated Column - 2 in UPBI All Customers Table
Result of Calculated Column - 2 in UPBI All Customers Table710×821 15.7 KB

Result 3
Result 3808×631 40.3 KB

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 ) )

Result 4
Result 4461×630 23.6 KB

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 -

Matched Customers Results
Matched Customers Results1130×638 67.7 KB

Unmatched Customers Results
Unmatched Customers Results1219×634 50.8 KB

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. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Supplier Match Example - Harsh.pbix (114.9 KB)

https://curbal.com/blog/glossary/lookupvalue-dax

3 Likes