cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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:

Orig1.JPG

 

Fig 2:

Orig 2.JPG

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Lookup values from disconnected table

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. Smiley Happy

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

c1.PNG

 

Regards

View solution in original post

2 REPLIES 2
Highlighted
Solution Specialist
Solution Specialist

Re: Lookup values from disconnected table

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.

 

Capture.PNG

Highlighted
Microsoft
Microsoft

Re: Lookup values from disconnected table

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. Smiley Happy

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

c1.PNG

 

Regards

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors