Forum Discussion

jtooke's avatar
jtooke
Helper I
7 months ago
Solved

Slicer and Visualization Hierarchy

Hi, I have a data set with a Region - SubRegion - Country hierarchy, with a slicer and visualizations using all 3 levels. My standard view shows all by Region with users being able to drill to lower...
Need Help
  • danextian's avatar
    danextian
    7 months ago

    Hi jtooke 

    This will require a disconnected table and another measure referencencing such. There are only two levels in the hierarchy below but notice that whenever a district is selected (level 1), the column chart automatically shows all schools within that district within the need to drilldown.

     

    Please see the attached pbix.

Amar_Kumar's avatar
Amar_Kumar
Super User
7 months ago

jtooke Not automatically with a hierarchy slicer.
But it is possible with a simple workaround.

 

  • Create 3 separate slicers: Region, SubRegion, Country

  • Use Country in your visuals

  • Selecting:

    • Region - shows all SubRegions/Countries under it

    • SubRegion - shows all Countries under it

    • Country - shows only that Country
      No DAX needed, works as expected.

  • jtooke's avatar
    jtooke
    Helper I
    7 months ago

    Thanks Amar_Kumar . Unfortunately this isn't quite what I need since I still need the Regional/SubRegional aggregation, where the default view should show each region, and when i select one region it should show the sub-regions for only that region. The countries should only show when one SubRegion is selected.

    • Amar_Kumar's avatar
      Amar_Kumar
      Super User
      7 months ago

      jtooke 

      What you’re trying to do can’t be handled by slicers alone. Slicers only filter data; they don’t control which level of a hierarchy a visual should display.

      The usual way to solve this is to let the visual decide the level based on what’s selected.

      Set your visual up with the full hierarchy on the axis:
      Region - SubRegion - Country
      (do not use drill down)

      Then create a small helper measure like this:

      Show Level = IF(HASONEVALUE(Geo[SubRegion]) || HASONEVALUE(Geo[Region]),1,1)

       

      Use your normal value measure for the visual, and add Show Level to the visual-level filters set to “is not blank”.

      Behavior:

      • No selection - visual shows Regions

      • One Region selected -visual shows SubRegions for that Region

      • One SubRegion selected - visual shows Countries

      • Multiple Regions selected - stays at Region level

      This keeps regional totals by default and naturally drills down based on the slicer selection, without users needing to click drill buttons.

      • jtooke's avatar
        jtooke
        Helper I
        7 months ago

        Thanks Amar_Kumar ! I'm not sure i fully understand, I have my Region-SubRegion-Country hierarchy as the Values for my slicer, and the X-axis for my visualization with that measure created and added to the visualization filters. However, when I select anything from the slicer it doesn't alter the behavior at all