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
Chris_Palmer
Regular Visitor

Create New Column using Lookup against Inactive Relationship

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

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