Parent-child hierarchy in slicer and tables

[dpombal]

Hi all,

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

EntityID	Name	Operations_Dep_View_Parent_ID
OPE999	ALL OPERATIONS	OPE999
A00049	BANK SANGLOB	OPE999
A00080	BANK SCF	OPE999
A00081	BANK SCF SUB A	A00080
A00082	BANK SCF SUB B	A00080
A00095	BANK BBGLOB	OPE999
A00097	BANK XXGLOB	OPE999

Fact Table

EntityID	Date	Accounts
A00049	01/01/2018	10
A00080	01/01/2018	3
A00081	02/01/2018	4
A00082	02/01/2018	2
A00049	03/01/2018	7
A00080	03/01/2018	5
A00081	04/01/2018	2
A00082	04/01/2018	1
A00095	01/01/2018	5
A00097	03/01/2018	10
A00095	04/01/2018	2
A00097	05/01/2018	4
A00049	15/01/2018	7
A00080	20/01/2018	5

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

A00049

BANK 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

[v-lili6-msft]

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

Step3:

Add

Parent-child hierarchy fields into slicer like this:

Result:

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

[dpombal]

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

[v-lili6-msft]

hi,@dpombal 

     You may try to use custom slicer HierarchySlicer in your report

and use drill down in Matrix visual.

Best Regards,

Lin

[dpombal]

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

[v-lili6-msft]

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.

Step 2:

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

Result:

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

[dpombal]

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)

Regards