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
nick-evans
Advocate I
Advocate I

Dealing with PATH Duplicates Errors?

I have a site that is built as a hierarchy.   

I am ultimately trying to distinguish if items in that hierarchy, at any point belong to a space with the name of 'Internal', because if so, they get treated specially.

Using PATH I can get a string that displays the hierarchy of a SpaceID.

 

PATH = PATH(Website[SpaceID], Website[ParentID]) 

I create a new column with the above code, and I successfully get results like:

 

path.png

 

When I try to use the Space Name to get a string of names rather ID's, I get this error:

PATH = PATH(Website[SpaceName], Website[ParentName]) 

Each value in 'Website[SpaceName] must have the same value in 'Website'[ParentName]. The value 'Phylum Internal' has multiple values.

 

According to this thread, it's because somewhere there are duplicate SpaceNames, and that makes it so the PATH function doesn't know which path to take.

 

I added a Count(Website[SpaceName]) and CountDistinct(Website[SpaceName]), and they are different values, indicating we do in fact have duplicate names in certain spaces.

I cannot change these space names, so I have to work around this problem.

 

 

My first thought was to try creating a new Parent & Child columns, that included their ID value, so the duplicates were not an issue.

ParentIDName = Website[ParentID] & " " & Website[ParentName]
SpaceIDName = Website[SpaceID] & " " & Website[SpaceName]

From there, I tried using path against the new values generated:

Path = PATH(Website(SpaceIDName), Website(ParentIDName))

But I get a new error: 

The value ' ' in 'Website'[ParentIDName] must also exist in 'Website'[SpaceIDName]. Please add the missing data and try again.

 

I'm not totally clear what is causing that one, so I'm on to trying new approaches.

 

What I would like to do is take the ID hierarchy, and perform a lookup on each ID in the string, returning its corresponding SpaceName.

 

ForExample:

Kingdom.png

 

In this data, the PATH value associated with SpaceName = Class, is 1000 | 1100 | 1200 | 1300.

I need to Split this string, and loop through each object from the split, doing a lookup against the value.

I have seen some DAX SPLIT articles about splitting each value in my delimited string to its own unique column, but because I don't know the depth for every row, it seems less ideal.

Here's a little mix&match code using C# to split and itterate through each object, with a DAX LOOKUP for my values.

Assuming this loop is being performed against Class where PATH value = 1000 | 1100 | 1200 | 1300.

 

nameList<string> list = new List<string>(); //to store each spacename during lookup
string[] spaceIDs = s.Split('|'); //splitting 1000 | 1100 | 1200 | 1300. foreach (string id in spaceIDs) { spaceName = LOOKUPVALUE(Website[SpaceName],Website[SpaceID],CONTAINERS_D_PARENTSPACEDETAILS[COMMUNITYID])
nameList.Add(spaceName & "|"); }

namePath = string.Join("|", nameList.ToArray());

 

The above code would ideally convert 1000 | 1100 | 1200 | 1300 into Domain | Kingdom | Phylum | Class.

 

I wish when the site had been setup, it had been uniquley named in these places, but since it wasn't, I'm left trying to work around it.

Also if there are other suggestions based on my needs that you think would work better - I'm open to new ideas as well.

 

1 ACCEPTED SOLUTION
nick-evans
Advocate I
Advocate I

Through a PM, this was the recommendation:

 

In your sceanrio, a child(SpaceName) may have multiple parents(ParentName), please follow the guide in the following blogs to solve this multi-parent-hierarchies issue.

http://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-pow...
http://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/

View solution in original post

4 REPLIES 4
nick-evans
Advocate I
Advocate I

Through a PM, this was the recommendation:

 

In your sceanrio, a child(SpaceName) may have multiple parents(ParentName), please follow the guide in the following blogs to solve this multi-parent-hierarchies issue.

http://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-pow...
http://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/

v-yuezhe-msft
Employee
Employee

@nick-evans,

I note that you post another similar thread which has been solved by creating the following column, does the column return your expected result?

Path = PATH(Table1[SpaceName], Table1[ParentName])

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

Unfortunately it does not.

In my sample data (which is what was being tested in the other thread) it works. 

However in my production data there are duplicates which present the various errors I outlined in this thread.

@nick-evans,

What version of Power BI Desktop do you use? Could you please share the working PBIX file and the problem PBIX file for me to test? You can upload PBIX files to OneDrive and send me shared link of the files via Private Message.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.