Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All
In the following example table I want to do a calculated column to look up from the application table to employer table returning the Parent Employer.
name.
I've filled in what the outcome should be in the applications table in red text.
Thanks in advance.
Applications table | Employer table | |||||||
EmployerID | Employer name | Parent Employer Name | ParentID | EmployerID | Employer name | |||
11 | Employer AA1 | Employer AA1 | 11 | 11 | Employer AA1 | |||
12 | Employer BB1 | Employer BB1 | 12 | 12 | Employer BB1 | |||
13 | Employer CC1 | Employer CC1 | 13 | 13 | Employer CC1 | |||
14 | Employer DD1 | Employer DD1 | 14 | 14 | Employer DD1 | |||
15 | Employer AA2 | Employer AA1 | 11 | 15 | Employer AA2 | |||
16 | Employer BB2 | Employer BB1 | 12 | 16 | Employer BB2 | |||
17 | Employer CC2 | Employer CC1 | 13 | 17 | Employer CC2 | |||
18 | Employer DD2 | Employer DD1 | 14 | 18 | Employer DD2 |
Solved! Go to Solution.
There are PATH functions that can handle recursive hierarchy structures like I think your Employer table is intended to have. See here for details on that: https://www.daxpatterns.com/parent-child-hierarchies/
For this, you can probably get away with two lookups like this:
Parent Employer Name =
VAR _ParentID =
LOOKUPVALUE (
Employer[ParentID],
Employer[EmployerID], Applications[EmployerID]
)
VAR _ParentName =
LOOKUPVALUE (
Employer[Employer name],
Employer[EmployerID], _ParentID
)
RETURN
_ParentName
Thank you so much. I will take a look into the link you have provided, I am not sure why the company chose to model the data like this as it is confusing. Unfortuanately, we use 3rd party software/databae so i am unable to change the model.
But your solution works so thanks and have a great day.
There are PATH functions that can handle recursive hierarchy structures like I think your Employer table is intended to have. See here for details on that: https://www.daxpatterns.com/parent-child-hierarchies/
For this, you can probably get away with two lookups like this:
Parent Employer Name =
VAR _ParentID =
LOOKUPVALUE (
Employer[ParentID],
Employer[EmployerID], Applications[EmployerID]
)
VAR _ParentName =
LOOKUPVALUE (
Employer[Employer name],
Employer[EmployerID], _ParentID
)
RETURN
_ParentName
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |