Forum Discussion
Grouping Hierarchy
So I have a column which has the following
1
1.1
1.1.1
1.1.2
1.1.2.1
1.1.2.1.2
1.2
1.2.1
1.3
i want to create a hierarchy so that when i click on 1, the next level would open which would be 1.1
In turn, everything associated with the 1.1 (1.1.1 and 1.1.2) would be next on the hierarchy and so on.
I'm wondering how to go about this?
Hello Anonymous,
Personally i would go about this using the PATH() Function to use this function you need to replace the "." with "|". Next you want to create a column for every possible outcome of the hierarchy. In your case it would look like the following:
Path 5 = VAR PathLen = PATHLENGTH('Table'[Column1]) VAR Delimiter = "." RETURN IF(PathLen >= 5; 'Table'[Path 4]&Delimiter&PATHITEM('Table'[Column1];5;TEXT);BLANK()) Path 4 = VAR PathLen = PATHLENGTH ( 'Table'[Column1] ) VAR Delimiter = "." RETURN IF ( PathLen >= 4; 'Table'[Path 3] & Delimiter & PATHITEM ( 'Table'[Column1]; 4; TEXT ); BLANK () ) => Continue untill Path 2 Path 1 = PATHITEM('Table'[Column1];1;TEXT)Kind regards
Joren Venema
Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.
2 Replies
Hello Anonymous,
Personally i would go about this using the PATH() Function to use this function you need to replace the "." with "|". Next you want to create a column for every possible outcome of the hierarchy. In your case it would look like the following:
Path 5 = VAR PathLen = PATHLENGTH('Table'[Column1]) VAR Delimiter = "." RETURN IF(PathLen >= 5; 'Table'[Path 4]&Delimiter&PATHITEM('Table'[Column1];5;TEXT);BLANK()) Path 4 = VAR PathLen = PATHLENGTH ( 'Table'[Column1] ) VAR Delimiter = "." RETURN IF ( PathLen >= 4; 'Table'[Path 3] & Delimiter & PATHITEM ( 'Table'[Column1]; 4; TEXT ); BLANK () ) => Continue untill Path 2 Path 1 = PATHITEM('Table'[Column1];1;TEXT)Kind regards
Joren Venema
Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.