Create New Column using Lookup against Inactive Relationship

[Chris_Palmer]

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

[Anonymous · Accepted 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

[Chris_Palmer]

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