cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saschatt Frequent Visitor
Frequent Visitor

Parent-Child Hierarchies with Date Dependencies

I am looking for a way to model a parent child relationship in which the parent is date dependant. For example, if we have an employee that switches teams I need to be able to represent which manager the employee reported to based on the date context.

 

employeeHierachy:

EmployeeId      ParentId      StartDate      EndDate

A                                          1/1/2016   

D                                          1/1/2019 

B                       A                 1/2/2017      2/1/2019

C                       D                 1/1/2016

B                       D                  2/2/2019      

 

factSales:

Deal     Employee     Amount    CloseDate

1          B                   500           2/1/2018

2          B                   150           6/20/2018

3          B                   400           5/1/2019

4          B                   200           7/30/2019

 

The goal is to be able to write a measure to attribute amounts at the top level of the heirarchy as follows

 

Lifetime Total Sales Amount:

EmployeeId     [TotalSales]

A                      650

D                      600

 

 

But when I try to create the calculated column "Path" in the employeeHeirarchy table as follows I receive the error below

 

Path = PATH(employeeHierachy[EmployeeId], employeeHierachy[ParentId])
 
Each value in 'employeeHierachy'[EmployeeId] must have the same value in employeeHierachy[ParentId]. The value 'B' has multiple values.

 

Does anyone know of a workaround for this?

  

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Parent-Child Hierarchies with Date Dependencies

For each and every date you have to replicate the whole table of dependencies. Once you have it, you connect your Date table to the date field in the expanded table and this is how you obtain a different table of dependencies for each and every date.

Best
Darek

View solution in original post

1 REPLY 1
Super User IV
Super User IV

Re: Parent-Child Hierarchies with Date Dependencies

For each and every date you have to replicate the whole table of dependencies. Once you have it, you connect your Date table to the date field in the expanded table and this is how you obtain a different table of dependencies for each and every date.

Best
Darek

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors