Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mikeborg82
Helper II
Helper II

Path function error

I am trying to build a relationship to break down our organization by employee reporting structure.  I get the following error:

Each value in 'Raw Data'[Person Name] must have the same value in 'Raw Data'[Supervisor Name]. The value 'i ,r ,r ,r ,' has multiple values.

 

Here is the DAX:

Path = PATH('Raw Data'[Person Name],'Raw Data'[Supervisor Name])

 

Person name does not have multiple values.  Any ideas?  Thank you.

1 ACCEPTED SOLUTION
TheOckieMofo
Resolver II
Resolver II

First off, there's a great blog post by the Italians on this topic here: Parent - Child Hierarchies. I've personally used their technique to successfully build a SSAS tab cube based upon essbase financial data with lots of ragged hierarchies.

 

Anyway, I believe I ran into this issue when I was building my cube. What I found was that there were "alternative hierarchies" in the base data that caused me to have duplicate "children." So in your application, I would examine your data. You likely have a member in the [person name] column that has multiple supervisors. Again, this may be because of an error in the base data and/or there's an alternative hierarchy listed in the data.

 

So I would go back to your query editor, remove duplicates from the [person name] column to test if your formulas will work then.

View solution in original post

6 REPLIES 6
RFordham
Regular Visitor

I have been trying to use the PATH function. I was not successful using my data so I created the same table shown on the www.daxpatterns.com/parent-child-hierarchies/. With the table and measure exactly the same as on the site, I still get an error in both PowerPivot and PowerBI. I then reproduced the sample that is shown on https://msdn.microsoft.com/en-us/library/gg492192.aspx, and still could not get the the path function to work.

 

Does anyone have any suggestion on where to go next?

 

I'm having the same issue - updated both Power BI and Excel 2016 - can't get PATH to work on either.

In Excel I get a vauge error related to missing ID that I'm certain are there - in Power BI I get an "unexpected error"

I'll test this on another machine tommorow but currently it's a dead end (been Googling for hours)

 

 

TheOckieMofo
Resolver II
Resolver II

First off, there's a great blog post by the Italians on this topic here: Parent - Child Hierarchies. I've personally used their technique to successfully build a SSAS tab cube based upon essbase financial data with lots of ragged hierarchies.

 

Anyway, I believe I ran into this issue when I was building my cube. What I found was that there were "alternative hierarchies" in the base data that caused me to have duplicate "children." So in your application, I would examine your data. You likely have a member in the [person name] column that has multiple supervisors. Again, this may be because of an error in the base data and/or there's an alternative hierarchy listed in the data.

 

So I would go back to your query editor, remove duplicates from the [person name] column to test if your formulas will work then.

Person Name has unique values.  There are obiously multiple occurences of supervisor, but that makes sense in an org hierarchy...

Anonymous
Not applicable

I just solved this issue for my hierarchy problem.  My boss' people give Power BI training to all the peeps in the company.  He wants me to create a hierarchy visual that shows all the high level managers (henceforth upper gods) and how many of their employees have taken the training.  He wants to shame the upper gods in a meeting by throwing up a chart that shows who is not training their employees.

 

So I'm given a data dump with the two colums, all the peeps in the company and the second column who they report to.  When I ran the Path Function the system kept telling me there were a ton of errors.  I figured out that some of the data had to be cleaned up (mispelling of names) so I did that and it solved about 90% of the issues.

 

The second issue is that Power BI kept telling me that there were duplicates in the first colunm that had to be removed but it was OK to have dups in the second column because multiple people in the company report to the same boss.  Hence dups in the second column.

 

Anywho Power BI kept throwing up an error and said that Bob Todd was duplicated in the first column (unique list of all the employees).  I tried everything I could think of but Bob Todd wasn't a dup.  Then I just did a manual slow painful process of checking to see if there were any dups at all in the first column, and there were, but it wasn't Bob Todd.  The duplicate was Jenn The Horrible Manipulator (of data).  Once I removed Jenn The Horrible Manipulator (of data) Power BI didn't give me an error.

 

Now I'm certain there is a good reason why Peanut Butter & Italianbread threw up an error but I really don't care.  Once I cleaned up the data the formula worked.  100% of the time it's loser user error.

Path = PATH('Raw Data'[Person Name],'Raw Data'[Supervisor Name])

 

Path required Person ID not name, and supervisior ID not Name .

 

This ID should be Number , eventhough  type could be Text or Number

 

https://dax.guide/path/

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.