Grouping Hierarchy

[Anonymous]

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?

[Anonymous · Accepted solution]

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.

[Anonymous]

Works a treat and good to know the Path() and PathItem() function