Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am building a report based upon the WorldwideImportersDW database. To simplify my data structure for users, I would like to use DAX to look up the Salesperson and Picker names and include them in the Order table.
I have the following [simplified] table and relationship structure:
================ ===============
ORDER EMPLOYEE
================ ===============
Salesperson Key --------(Active)-------- Employee Key
Picker Key - - - -(Inactive) - - - - - - ^
Name
I have the Salesperson lookup working using the RELATED function on the Active relationship:
Salesperson = RELATED(Employee[Name])
What I can't work out is how to add a column to look up the Picker based upon the Inactive relationship. I have tried the following, but it is not returning the correct result (only returns a value where the salesperson == picker):
Picker = LOOKUPVALUE(Employee[Name],Employee[Employee Key],'Order'[Picker Key])
Any ideas?
Many thanks,
Chris
Solved! Go to Solution.
No idea why LOOKUP does not work for you... It does for me. But here's another method:
var __currentPicker = 'Order'[Picker Key] return SUMMARIZE( FILTER( Employee, Employee[Employee Key] = __currentPicker ), Employee[Name] )
Best
Darek
No idea why LOOKUP does not work for you... It does for me. But here's another method:
var __currentPicker = 'Order'[Picker Key] return SUMMARIZE( FILTER( Employee, Employee[Employee Key] = __currentPicker ), Employee[Name] )
Best
Darek
@Anonymous It works! Many thanks for your help. I now have:
Picker = (var __currentPicker = 'Order'[Picker Key] return SUMMARIZE( FILTER( Employee, Employee[Employee Key] = __currentPicker ), Employee[Name] ) )
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |