Parent-child matrix visualization with parent as first child row

[Anonymous]

Given the following table

ID	Parent ID	AMOUNT	PATH
1		10	1
2	1	20	1|2
3	1	30	1|3
4	2	40	1|2|4
5	2	50	1|2|5

I want to create a Parent-Child visualization using matrix in Power BI.

Currently using DAX method PATH and PATHITEM I have this:

  ID                 Amount       

   1                  150

     2                110

           4          40

           5          50 

     3                30

Is it possible to have :

  ID                  Amount       

   1                   150

       1               10

       2               110

           2           20

           4           40

           5           50 

       3               30

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduc your scenario. The pbix file is attached in the end.

Table:

You may create calculated columns as below.

Path = PATH('Table'[ID],'Table'[Parent ID])
Level1 = PATHITEM('Table'[Path],1,TEXT)
Level2 = 
var _level2 = PATHITEM('Table'[Path],2,TEXT)
return
IF(
    PATHITEM('Table'[Path],2,TEXT)="",
    [Level1],
    _level2
)
Level3 = 
var _level3 = PATHITEM('Table'[Path],3,TEXT)
return
IF(
    _level3="",
    IF(
        [Level2]="",
        [Level1],
        [Level2]
    ),
    _level3
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-alq-msft  that's what I wanted to do.

Just one last thing is it possible to avoid to repeat the node "1"  three times?

(-)1  

    1

instead of

(-)1

     (-)1

           1

[amitchandak]

@Anonymous , refer if this can help

https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi-with-power-query/

[Anonymous]

Thank you @amitchandak for your reply but in my case I don't have mutliple parents, when I unfold a parent node I just want to display the parent node among the children nodes, because the parent node has his own "amount" to display.