Child Parent Hierarchy with multiple parent - show part by choosing an element

[Anonymous]

Hello,

I have a table with child-parent hierarchy with multiple parent and I need to filter it if I choose a particular element.

Here is my table :

path	parentPath	idElement
1		13
1|2	1	14
1|2|3	1|2	15
1|2|4	1|2	16
1|5	1	17
1|6	1	18
7		14
7|8	7	19
9		20
10		21
11|12	11	22

Now, if I choose an element, I want to keep parents and children, espacially in the case where the element appeared multiple times.

Here is an exemple if I choose element 14:

path	parentPath	idElement
1		13
1|2	1	14
1|2|3	1|2	15
1|2|4	1|2	16
7		14
7|8	7	19

It needs to work even if there is a parent of a parent or a child of a child.

Thank you !

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table:

Table2:

create with below dax formula

Table 2 = VALUES('Table'[idElement])

2. create a measure with below dax formula

Measure =
VAR _ie =
    SELECTEDVALUE ( 'Table 2'[idElement] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[idElement] = _ie )
VAR tmp1 =
    CALCULATETABLE ( VALUES ( 'Table'[path] ), tmp )
VAR tmp2 =
    CALCULATETABLE ( VALUES ( 'Table'[parentPath] ), tmp )
VAR cur_ie =
    SELECTEDVALUE ( 'Table'[idElement] )
VAR cur_path =
    SELECTEDVALUE ( 'Table'[path] )
VAR cur_ptpath =
    SELECTEDVALUE ( 'Table'[parentPath] )
RETURN
    SWITCH (
        TRUE (),
        cur_ie = _ie, 1,
        cur_path IN tmp2, 1,
        cur_ptpath IN tmp1, 1,
        ISBLANK ( _ie ), 1
    )

3. add a slicer visual with Table2 field, add a table visual with Table fields, add measure to table visual filter pane and apply it

 

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.