Forum Discussion

jl20's avatar
jl20
Helper IV
9 years ago
Solved

Lookup values from disconnected table

Hello,

 

I am struggling with an issue relating to lookups, which is best illustrated by example. Ultimately, I want to somehow pull

 

- Matter Data is the fact table (contains all numbers, multiple instances of each matter depending on who the timekeeper is)

- Dim Fact Matter has qualitative information about each matter (one matter per line, which serves as key)

- Dim Timekeep has name, location, title, etc. of each person in the system, identified by a unqiue 4 digit ID (exactly instance of each on this table). Related by Working Timekeeper (fact) / Timekeeper (dim) fields.

- Matter Originators (Fig 2) has one matter # per record, and lists out the various people who sold the job (by timekeeper ID).

 

My questions are:

1. How would I go about relating this to the Dim Fact Matter (or Matter Detail) table AND have the names pull through based on the unique 4 digit ID number? If I link to Fact Matter by matter number, I can't pull the names since DIM timekeeper is linked to Working Timekeeper on the fact table.

 

2. Is there a way to do a lookup without using the RELATED function? 

 

Fig 1:

 

Fig 2:

 

Thanks!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi jl20,

     

    If I understand you correctly, you should be able to use LOOKUPVALUES function to create new calculate columns in the Timekeep table to get the names pull through based on the unique 4 digit ID number without any relationships. The formula below is for your reference. :smileyhappy:

    Orig_Prim_Name =
LOOKUPVALUE (
    Timekeep[Name],
    Timekeep[Timekeeper], 'Matter Originators'[Orig_Prim]
)

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi jl20,

     

    If I understand you correctly, you should be able to use LOOKUPVALUES function to create new calculate columns in the Timekeep table to get the names pull through based on the unique 4 digit ID number without any relationships. The formula below is for your reference. :smileyhappy:

    Orig_Prim_Name =
LOOKUPVALUE (
    Timekeep[Name],
    Timekeep[Timekeeper], 'Matter Originators'[Orig_Prim]
)

     

    Regards

  • Aron_Moore's avatar
    Aron_Moore
    Solution Specialist
    9 years ago

    I use lookupvalue in this model which doesn't use related to get text for the account number. You may need to create another key column by concatenating a couple of columns.