Forum Discussion
Helper IHierarchy bar chart based on measures
Hi gfdytr4 ,
Thank you for reaching out to the Microsoft fabric community forum. Also thank you danextian
As mentioned by rohit1991 , Power BI doesn’t natively support measure-level drill-down in bar/column charts.
The most reliable approach is using a Matrix visual, where you can place measure labels (with levels) on rows and time on columns, allowing drill-up/down natively.
I hope this is clear. If you have any further questions, please feel free to reach out.
Thank you.
Super UserHi gfdytr4
Yes, you can simulate a two-level hierarchy in a bar chart using measures by combining a disconnected table with a dynamic DAX measure. Power BI doesn’t support native drill-down between measures, but this approach replicates the behavior effectively.
First, create a disconnected table named "MeasureSelector" with two columns: one for level and one for measure labels. For example:
Level | Measure Name
1 | Total Consultants
2 | Company A Consultants
2 | Company B Consultants
Then, create a new measure using SWITCH to return the appropriate value based on the selected measure name:
Selected Measure =
SWITCH(
SELECTEDVALUE('MeasureSelector'[Measure Name]),
"Total Consultants", [Total Consultants],
"Company A Consultants", [Company A Consultants],
"Company B Consultants", [Company B Consultants]
)In your bar chart:
-
Place Month on the X-axis
-
Place the Selected Measure on the Y-axis
-
Add MeasureSelector[Measure Name] to the Axis or Legend
-
Enable drill-down on the visual
Now you will see level 1 showing Total Consultants, and when you drill down, it will break into Company A and Company B consultants. This simulates a hierarchy-like drill experience using measures without the need for slicers.
Helper IThis is what i want but placing Measure name in the legend returns all 3 columns. The drill up/down icons dont appear. There is no enable drill-down for clustered column chart that i can find.
Placing the measure name in Y-axis defaults to count and does not make the drill up icons apprear in the visual
- rohit1991
Hi gfdytr4
You're correct that clustered column charts in Power BI don't support drill-down when using a text field like "Measure Name" from a disconnected table in the legend. That’s why all three measures appear at once, and no drill icons are shown. To achieve your goal of a two-level hierarchy where level 1 shows Total Consultants and level 2 breaks it down into Company A and Company B ,the best and fully supported solution is to use a Matrix visual.
To resolve it, Create a disconnected table with your measure labels and their levels (e.g., Total Consultants = Level 1, Company A/B = Level 2). Then, create a SWITCH-based dynamic measure that returns the appropriate value based on the selected label. In the matrix, place Month on the columns, Measure Name on the rows, and your dynamic measure on values. This setup supports drill-up/down natively within the matrix, so the user can expand from total to breakdown without any slicer.