PATH function error

[jhartranft60]

I'm attempting to build a quarterly hierarchy.  I'm creating this to track our yearly performance appraisal requirements, so the hierarchy is used for both row-level security as well as rolling up colleague performance measures to the appropriate leaders.  Since colleagues can change teams over the course of the year, the staffing is tracked quarterly.  Due to this, I created a "Unique ID" for each colleague (Concat Employee ID, "-" & Quarter).  I've verified that there are no duplicates in the Unique ID field.  Below is the formula I'm using and the error I'm getting.  It seems DAX doesn't like that there are multiple values in the 'Manager ID' field over the course of the year.  I'd hoped that using the CALCULATE function would fix my problems, but obviously I was mistaken.  I've run out of ideas on how to make this work.  Thoughts and suggestions are highly appreciated!!

"Each value in 'Staff by Quarter'[Employee ID] must have the same value in 'Staff by Quarter'[Manager ID].  The value '4567' has multiple values."

Hierarchy = CALCULATE(PATH('Staff by Quarter'[Employee ID],'Staff by Quarter'[Manager ID]),FILTER('Staff by Quarter','Staff by Quarter'[Quarter]=EARLIER('Staff by Quarter'[Quarter])))

Employee ID	Quarter	Unique-ID	Manager ID
1234	Q1	1234-Q1	9999
1234	Q2	1234-Q2	9999
1234	Q3	1234-Q3	9999
1234	Q4	1234-Q4	9999
4567	Q1	4567-Q1	7777
4567	Q2	4567-Q2	8888
4567	Q3	4567-Q3	8888
4567	Q4	4567-Q4	8888

[mahoneypat · Accepted solution]

FYI that you likely need to address the distinct parent issue, but you can use the Bonus Function at the end of this article to perform the equivalent of the PATH function in your query (and it doesn't have the single parent limitation).

Guest Post: Using List.Accumulate for Input/Output Genealogy – The BIccountant

Pat

[selimovd]

Hello @jhartranft60 ,

if you use the PATH function each item must have a distinct parent. So the problem is that employee 4567 has managger 8888 and manager 7777. 

Also be aware that PATH doesn't work with row level security:
"This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules."

Sorry for that, but I think this approach doesn't work.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[jhartranft60]

Hi @selimovd 

I use the hierarchy column created by the path function for the RLS.  I use the PATHCONTAINS function to search the hierarchy column for the userprincipalname.  I use this on many of my datasets, so I know it works well.  

I guess I don't understand why my calculate function doesn't work to filter out the multiple managers.  The second half of the function "Filter('Staff by Quarter','Staff by Quarter'[Quarter] = EARLIER('Staff by Quarter'[Quarter])" should filter the table down to just one Quarter and therefore eliminate the fact that there are different Manager IDs for different quarters.  That's the part I'm attempting to figure out how to tweak to make this thing work.

[selimovd]

Hey @jhartranft60 ,

you get the error in the first half of the measure:

CALCULATE(PATH('Staff by Quarter'[Employee ID],'Staff by Quarter'[Manager ID])

Here the employee 4567 has manager 8888 and manager 7777. This ambiguity is not allowed, so there will be an error.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic