Forum Discussion

Lexicron's avatar
Lexicron
Frequent Visitor
5 years ago

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