Forum Discussion

maashi's avatar
maashi
Frequent Visitor
2 years ago

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...
Need Help
Irwan's avatar
Irwan
Super User
2 years ago

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

 

then create new measure 

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

 

Here i tried matching up this DAX with your sample data

 

 

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's avatar
    maashi
    Frequent Visitor
    2 years ago

    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.