The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Hi,@maashi I am glad to help you.
Hello,@Irwan thanks for your concern about this issue.
Your answer is excellent!
May I ask if you have found a suitable solution, and if you have, please share them as it will help more users with similar problems. Or you can mark the valid suggestions provided by other users as solutions.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello @maashi
please check if this will acommodate your need.
First, create a new calculated column
then create new measure
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
33 | |
20 | |
17 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
28 |