Reply
Highlighted
Member
Posts: 130
Registered: ‎02-23-2017

Parent-child hierarchy in slicer and tables

Hi all,

I am looking for advice on how to use this fair simple Parent Child dimension.

 

 

1 parent child entity dimension.PNG

 

 

EntityIDNameOperations_Dep_View_Parent_ID
OPE999ALL OPERATIONSOPE999
A00049BANK SANGLOBOPE999
A00080BANK SCFOPE999
A00081BANK SCF SUB AA00080
A00082BANK SCF SUB BA00080
A00095BANK BBGLOBOPE999
A00097BANK XXGLOBOPE999

 

Fact Table

EntityIDDateAccounts
A0004901/01/201810
A0008001/01/20183
A0008102/01/20184
A0008202/01/20182
A0004903/01/20187
A0008003/01/20185
A0008104/01/20182
A0008204/01/20181
A0009501/01/20185
A0009703/01/201810
A0009504/01/20182
A0009705/01/20184
A0004915/01/20187
A0008020/01/20185

 

2 Fact table.PNG

 

 

I would require to have a slicer  with typical Behavior, if filtering ALL Operations , all member displayed, and for example if selected sum all accounts of  children from A00049 BANK SANGLOB

A00049BANK SANGLOB

 

 

 

It could be good to have any tabular graph, for slicing and drill/ down like pivot table / matrix  but not sure how to do this with powerbi.

 At least filtering and getting children from a member should be  a must

 

 

Regards

Community Support Team
Posts: 1,341
Registered: ‎07-30-2018

Re: Parent-child hierarchy in slicer and tables

hi,@dpombal 

     You may try to use custom slicer HierarchySlicer in your report

and use drill down in Matrix visual.

 

5.PNG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 130
Registered: ‎02-23-2017

Re: Parent-child hierarchy in slicer and tables

I have used Drill down on visuals when I have several fields ( Continent--> country --> State).

but how Can i configure it with my current tables, can you please assist me initial steps.

I haven't see it on this blogs ! thanks

Community Support Team
Posts: 1,341
Registered: ‎07-30-2018

Re: Parent-child hierarchy in slicer and tables

hi,@dpombal

    After my research, Drill down function is not supported in table visual for now, and you use drill down function 

in maritx visual. 

and you do these follow my steps like below:

Step 1:

drag the hierarchy fields into Rows or Columns together.

8.PNG

Step 2:

click one of these two icons to drill down next hierarchy。

9.PNG

Result:

10.PNG

And if you want to use Drill down on table visual, you could post your new idea in Power BI ideas and make this feature coming sooner.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 130
Registered: ‎02-23-2017

Re: Parent-child hierarchy in slicer and tables

Your solution is valid for a fixed hierarchy with several columns like Continent--> Country --> State

 

However my business case is with a Parent child

 

Check screenshot, I only have column with Entity ID and other with Parent Entity ID.

And I would want to collapse , expand drill like textbox on below  text box ( imagine a dynamic table of excel with collapse expand)

 

3 required chart.PNG

 

Regards

Community Support Team
Posts: 1,341
Registered: ‎07-30-2018

Re: Parent-child hierarchy in slicer and tables

HI,@dpombal

       After my research, you may try to do these as below:

Step 1:

use SEARCH Function to add a Parent-child hierarchy 

hierarchy = 
IF (
    SEARCH ( "BANK SANGLOB", Table1[Name], 1, 0 ) > 0,
    "BANK SANGLOB",
    IF (
        SEARCH ( "BANK SCF", Table1[Name], 1, 0 ) > 0,
        "BANK SCF",
        IF (
            SEARCH ( "BANK BBGLOB", Table1[Name], 1, 0 ) > 0,
            "BANK BBGLOB",
            IF ( SEARCH ( "BANK XXGLOB", Table1[Name], 1, 0 ) > 0, "BANK XXGLOB" )
        )
    )
)

Step2:

Import custom slicer from Marketplace

5.PNG

Step3:

Add

Parent-child hierarchy fields into slicer like this:

24.PNG

Result:

25.PNG

By the way, you need not add ALL OPERATIONS in the column Name, if you don't select any value from slicer,

it will show all the data, So you may remove the first row.

 

here is pbix, please try it.

https://www.dropbox.com/s/hd8dsz5anyvir2r/Parent-child%20hierarchy%20in%20slicer%20and%20tables.pbix...

 

Best Regards,

Lin

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 130
Registered: ‎02-23-2017

Re: Parent-child hierarchy in slicer and tables

And if my table has 2500 entities? should I anidate 2500 if statements, i think this solution is not valid for me,

thanks for your approach