Forum Discussion
Lexicron
5 years agoFrequent Visitor
How to toggle slicer between behaviors: explicit select vs hierarchy select including children?
Hello.
We've got a project hierarchy. The Project IDs are like so:
1 Department of XXX
1.1 Requirements Gathering $20
1.1.1 Finance $15
1.1.2 Field Services $15
1.2 System Implementation $10
1.2.1 Configuration $5
1.2.1.1 Report Customisation $5
1.2.2 Data Migration $30
Important points:
- Transactions can be entered against any project ID. New child projects can be created even if their parent projects have had transactions entered against it.
- Any project ID's transaction amount or balance DOES NOT include the transactions of the child IDs nested under it
- e.g. If 1.1 reports a $20 balance, that $20 is a sum of its own transactions. There is no drilldown of that $20 into 1.1.1 and 1.1.2. The children 1.1.1 and 1.1.2 will have their own balances that do not contribute to that $20. There is no aggregation or rollup.
- We need to display that aggregation in the PowerBI report (Requirement A). We also need to display single selected project ID and its own balance (Requirement B). We need a toggle to switch between the two.
- e.g Requirement A: select 1.1 in slicer, display report as if 1.1 and 1.1.1 and 1.1.2 are all selected, $50.
- e.g Requirement B: select 1.1 in slicer, display report for 1.1 only, $20.
- We need multi-select for both requirements.
- e.g Requirement A: select 1.1 and 1.2 in slicer, display report as if 1.1, 1.1.1, 1.1.2, 1.2, 1.2.1, 1.2.1.1, 1.2.2 are all selected, $100.
- e.g Requirement B: select 1.1 and 1.2 in slicer, display report for 1.1 and 1.2 only, $30.
Problems:
- PowerBI slicer works for Requirement B.
- PowerBI slicer display is not ideal for Requirement A.
- To turn the slicer into a hierarchy slicer, we need to flatten the hierarchy by adding columns that split the ID into their generations. This means for each generation a column needs to be manually created, and not all projects are the same number of generations deep. Some projects will have BLANK under Level 4 if they don't have great-grandchildren. These BLANK values will show up in the slicer. To hide them, we can set the filter for each level to "is not blank". However doing this to the Level 4 filter causes the entire project family to not show up if they don't have a child project 4 generations deep.
- When 1.1.1 is selected, it displays 1 (Level 1) + 1.1 (Level 2) + 1.1.1 (Level 3). We just want the selected 1.1.1 to display.
- What is the best way to toggle between both Requirements?
1 Reply
- v-xulin-mstf
Community Support
Hi Lexicron,
This depends on your data structure.
Could you provide sample data and expected output after removing sensitive data?
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Best Regards,
LinkIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.