Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Data hierarchy - Drilldown and Slicers to update data level

Hello,

 

I am using the below table which has the following columns (has been filtered to limit rows). The data_level column  determins whether the data is national (national_1), country (national_3), region (region), county (county), local authority (local_authority).

 

What I need to create:

  • Bar chart which can be drilled down from the highest level 'national_1', 'national_3', 'region', 'county' and to the lowest 'local_authority'
  • At each level of the drill down I want the data to point to the sum (numeric_value) of that data level e.g. if the chart is drilled down to region level data, then I want the total numeric_value for all regions under that country (national_3)
  • I want to be able to use a slicer which will also drill the data down to the correct data level e.g. if I select Kent in the 'County' slicer then I would want the visual to filter to 'county' level data
  • The data cannot be summed from the lowest level being local_authority as each data level pulls from a different data source.
national_1national_3regioncountylocal_authoritydata_levelnumeric_value
United KingdomEngland   National_3453400066
United KingdomEnglandSouth East  Region179523
United KingdomEnglandSouth EastKent County893
United KingdomEnglandSouth EastKentThanetLocal_Authority68
United KingdomEnglandSouth EastKentSwaleLocal_Authority89
United KingdomEnglandSouth EastKentMaidstoneLocal_Authority65
United KingdomEnglandSouth EastKentCanterburyLocal_Authority65

 

I have been stuck on this issue all week and would love to some help to conquer this issue, thank you in advance! 🙂

 

The below is what I have tried and it nearly works but doesn't like multiple regions being selected for the same country etc.

 

= SWITCH(TRUE()
----COUNTRY
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[national_3]) && HASONEVALUE(Consolidated_LLMIT[region]) = FALSE(), _VolumeCountry
----REGION
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[region]) && HASONEVALUE(Consolidated_LLMIT[county]) = FALSE(), _VolumeRegion
----COUNTY
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[county]) && HASONEVALUE(Consolidated_LLMIT[local_authority]) = FALSE(), _VolumeCty
----LOCAL AUTHORITY
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[local_authority]), _VolumeLocalAu
----NATIONAL
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" , _VolumeNational
,0)
0 REPLIES 0

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.