Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
maashi
Frequent Visitor

Replicating an excel VLOOKUP function

Hi there

 

I would like to replicate a VLOOKUP type function in Power BI. 

I need to look up a column of sales order numbers to see if it has been used in the past. Currently in excel I do it as a vlookup, then eliminate double ups, copy and paste the final column into another worksheet and count this final column.

Would like to replicate all this with a measure. 

Have tried to use the CONTAINS function to mimic the VLOOKUP, then hoping to DISTINCTCOUNT the result. But i don't think it wants to work in VAR/RETURN - do I have to define a measure within a measure.

 

Here is what I have tried

NCP on NCP =
Var NCPonNCPList =
    FILTER (
        ALL ( fact_NCP_Reporting ),
        CONTAINS (
            fact_NCP_Reporting,
            fact_NCP_Reporting[Sales Order No], fact_NCP_Reporting[Original Sales Order]
        )
    )
RETURN
    DISTINCTCOUNT (NCPonNCPList)
 
I hope the below sample data link works to show what i have been doing in Excel, I do it on a monthly basis. 
 
Thank you
 
Marcy

 

2 REPLIES 2
Irwan
Solution Supplier
Solution Supplier

hello @maashi 

 

please check if this will acommodate your need.

 

First, create a new calculated column

Working Column =
var _order = 'Table2_2'[Original Sales Order]
Return
MAXX(FILTER('Table2_2','Table2_2'[Sales Order No]=_order),'Table2_2'[Sales Order No])

Irwan_1-1718684352206.png

 

then create new measure 

NCP on NCP = DISTINCTCOUNT('Table2_2'[Working Column])

Irwan_0-1718684303857.png

 

Here i tried matching up this DAX with your sample data

Irwan_2-1718685317086.png

Irwan_3-1718685470878.png

 

 

Also, i dont know if you provide dummy data, but the sample data you provide has wrong date format (date and month placement are switched). Please make sure your date format is correct before proceeding to next calculation.

 

Hope this will help you.

Thank you.

maashi
Frequent Visitor

Thanks. Yes it is dummy data, plus I'm in New Zealand, we use DDMMYYYY. 

I don't suppose there is a solution without creating a calculating column? I felt like I was doing it already in an extremely clumsy way in Excel, with the extra working columns, but it is much easier to add columns in excel so I just carried on with it.  The dataset I will be using is quite big and growing daily so I was hoping not to add any columns in, but if there isn't, I'll definitely have a go with this solution.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors