Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Best Regards,
Lin
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
hi,@dpombal
You may try to use custom slicer HierarchySlicer in your report
and use drill down in Matrix visual.
Best Regards,
Lin
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |