Forum Discussion

Chris_Palmer's avatar
Chris_Palmer
Regular Visitor
7 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    7 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Chris_Palmer
      Regular Visitor
      7 years ago

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