Forum Discussion
parent-child hierarchy - duplicate error when trying to sort
Hi,
I created a parent -child hierarchy using path and pathitem. It has 5 levels (not really relevant).
When I try to sort each level it says that it cannot do it, because it has a duplicate value. In reality it doesn't have a duplicate value.
I used to have a link to a webpage which solved this issue, but stupid me, I deleted that. I'm pretty sure it has something to do with the path function, because when I copy the table to Excel and import it as an excelsheet in a new report it does let me sort the columns.
Any idea on how to solve it? Thank you.
Hi,lekkerbek
Regarding the issue you raised, my solution is as follows:
1.First of all, according to the information we have gathered about similar situations, the reason why the above situation may be related to your storage mode, only in the IMPORT storage mode, there will be reservations about the hierarchy, such as the Direct query mode, it may have an impact on your hierarchy.
You can view the storage schema of your table in the model view in powerbi.
Here are the links to the relevant content:
Solved: Re: Using Power BI Hierarchies in Excel - Page 2 - Microsoft Fabric Community
2.Please check if the duplicates in your parent-child columns are the same, in the PATH() function, if the duplicate child ids point to the same parent id then he will return the normal content, if the duplicate child ids point to different parent ids then there will be an error, quoting the example in the documentation is: if you have two lists of the same employee IDs and they have the same manager ID, then the The PATH() function will return the manager's ID, but if there are two identical employee IDs with different manager IDs, the PATH() function will return an error.
Here are the links to the relevant content:
PATH function (DAX) - DAX | Microsoft Learn
3.Please check that the parent and child columns you are calling contain calculated columns or that the table itself has RLS, as the PATHITEM() function does not support use in calculated columns or Row Level Security (RLS) rules in DirectQuery mode.
Here are the links to the relevant content:
PATHITEM function (DAX) - DAX | Microsoft Learn
4.Here is a link to the relevant documentation which I hope you find helpful:
Understanding functions for parent-child hierarchies in DAX - DAX | Microsoft Learn
Parent-child hierarchies – DAX Patterns
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi,lekkerbek
Regarding the issue you raised, my solution is as follows:
1.First of all, according to the information we have gathered about similar situations, the reason why the above situation may be related to your storage mode, only in the IMPORT storage mode, there will be reservations about the hierarchy, such as the Direct query mode, it may have an impact on your hierarchy.
You can view the storage schema of your table in the model view in powerbi.
Here are the links to the relevant content:
Solved: Re: Using Power BI Hierarchies in Excel - Page 2 - Microsoft Fabric Community
2.Please check if the duplicates in your parent-child columns are the same, in the PATH() function, if the duplicate child ids point to the same parent id then he will return the normal content, if the duplicate child ids point to different parent ids then there will be an error, quoting the example in the documentation is: if you have two lists of the same employee IDs and they have the same manager ID, then the The PATH() function will return the manager's ID, but if there are two identical employee IDs with different manager IDs, the PATH() function will return an error.
Here are the links to the relevant content:
PATH function (DAX) - DAX | Microsoft Learn
3.Please check that the parent and child columns you are calling contain calculated columns or that the table itself has RLS, as the PATHITEM() function does not support use in calculated columns or Row Level Security (RLS) rules in DirectQuery mode.
Here are the links to the relevant content:
PATHITEM function (DAX) - DAX | Microsoft Learn
4.Here is a link to the relevant documentation which I hope you find helpful:
Understanding functions for parent-child hierarchies in DAX - DAX | Microsoft Learn
Parent-child hierarchies – DAX Patterns
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.