Lookup values from disconnected table

[jl20]

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!

[v-ljerr-msft · Accepted solution]

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. 

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

Regards

[Aron_Moore]

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.