Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |