Forum Discussion
Solved
Creating Hierarchies in Power BI Desktop Data Model
Is there a plan to add the ability to create hierarchies in the Power BI Desktop data model like there is in the Excel Power Pivot data model, and then make those available to be used in reports?
- Yes there is :) We're working on this now and we'll have it available in a future release. No firm timelines, but hopefully in the next couple of months.
Thank you for you quick contribution.
Yes, I have tried. And yes it's starting to make me tired.
No only this is not solving the issue that requires hierarchy (or additional feature to reproduce hierarchy) I tried to explain in my previous post. But this is also just not operating as it should be either. I added on the x-axis another field (Department) to my date field. Great. You drill down into Department on month January 2016, great you see the dollars for January 2016 for each Department, and bam, going up again back to Date, my filter (date to date) on the date is gone. Ridiculous. Not usable.
Fortunately we are not paying for this yet, fortunately we are not in Production yet.
Long way to go. Long way to loose potential customers.
MichaelP, you've raised several points.
- Creating ad-hoc hierarchies in area charts axis
Currently area and line charts do not allow drill down on the x axis. This is by design, and I believe drill down on their x axes will be enabled in the future, but I'm not certain on that feature. If you were to put a hierarchy into the well for the axis for any of these chart types, there would be no way to display the hierarchy values. The default behavior is to just display the top-level field as the axis. The ability to create a hierarchy in Power BI would have no impact on this functionality. - Adding a hierarchy to the legend field well
There is no visual in Power BI that allows a hierarchy (ad-hoc or formal) to be used in the legend that also has another categorical field well (Location, Axis, Group). There is a subtle indicator in the field well that indicates whether more fields can be added to that well or not. If the border of the field well has a bit of a gap at the bottom, then mroe fields can be added, creating an ad-hoc hierarchy, or indicating that a formal hierarchy could be used there. If the border is equally close to the field name on the top and bottom, then only a single field is allowed there. The presence or absence of a formal hierarchy makes no difference whatsoever here. If you add a formal hierarchy to one of these wells, only the top level will be displayed. This is by design.
Drill down occurs on axes, groups, or locations (charts, treemap, map), not on the legend. The only visualization family that allows drill down on the legend are the circular visuals: pie and donut. In these, the legend is drillable, because it is filling a role similar to an axis in a linear chart. This design decision is because it would be very confusing to allow two drill-down paths. Only one element is drillable in any visualization. - Filter interactions with drilling
There are three ways to filter data on the Power BI canvas: filters - visual-level, page-level, and report-level, all defined on the right hand 'Filters' pane below the visualization pane; slicers; and visual brushing and linking. Brushing and linking is the term for the cross-highlighting you see when selecting a single axis category in one chart - the remainder of the visualizations are highlighted or filtered (based on the relations defined in the 'Visual Interactions' ribbon menu).
Filters and slicers are not impacted by drilling through hierarchies in visuals.
Brushing and linking are currently limited to only a single filter at a time. This does not play well with drill-down in other visuals. Drilling in one visual will clear the brushing and linking selection in another. Drill-down will not apply filters to any visualizations other than the one being drilled. These are both issues that are under review in the Power BI ideas forum (links below). I believe this is the issue you are describing in your most recent post, and it is quite annoying. The "workaround" is to use slicers and filters for filtering if multiple selections must be made.
All of this behavior is completely independent of whether or not hierarchies can be defined and consumed in a Power BI model. When using a live connection with SSAS Multidimensional that has hierarchies defined (PBI can consume Multidimensional hierarchies), none of this behavior is different.
Here are the links to vote for and discuss multiple brushing and linking selections and drill down filtering: