Creating Hierarchies in Power BI Desktop Data Model

[bblais]

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?

[WillT · Accepted solution]

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.

[dascazubi]

Do you have any news of this?

[LanceDelano]

We've started on it. 

[JohnFetherolf]

Since the feature has been started, can you provide a release date for creating hierachies in power bi desktop

[greggyb]

I don't know about the release date for creating hierarchies in PBI Desktop, but they are really only a report authoring convenience.

You can achieve identical functionality by just stacking the fields you're interested in in the axis / category / location field well for various visualizations. You can easily add [Year] and [Quarter] to a chart to enable drill down, without having a defined hierarchy on these fields.

[MichaelP]

I would be happy with your answer that this is just convenience if that would be true...

But this is not. Example:

Can you explain how then to add on this the hierarchy I would like to use (Service Departments -> Projects) ? Ho. I can past screenshot..  Nice.

So Area Diagram, X-Axis "dates", Y-Axis: "Amounts", Legend: "Service Departments".

Can you add to Legend in area charts "Projects" to have the drilldown from "Service Departments"?

On my experience: NO.

Context: Tabular Model, Analysis Service

[Mi2n]

@MichaelP I was having such similar requirements of changing/drilling the dimension in the legend of a chart. Recently I stumbled upon a way to implement this by tweaking the data model slightly.

Suppose you have a Fact table with the following fields,

Date, Dept, Proj, Amounts

Now, we just create a new column which will act as a composite key for both Dept and Proj, by concatenating the values in both the fields separated by any character (underscore would do fine). Lets name the new column Dept_Proj. So we would now have the below fields in our fact table.

Fact:

Date, Dept, Proj, Amount, Dept_Proj

Next step would be to create another table which would take just the Dept_Proj field from our fact table and deduplicate it. Lets name this table as Dept_Proj_unique and this is gonna have just one field Dept_Proj (the deduplicated field from the fact)

Dept_Proj_unique:

Dept_Proj

Next, we need to create a table by referencing our Dept_Proj_unique table and split up the Dept_Proj field into Dept and Proj separately. We need to delete one of the field(say Proj) and have the other field only. Now, introduce a new field which will have the field name of the remaining field like "Dept".

So, now we are presented with a table which has the fields,

Dept_Proj - The concatenated field

Dim_value - The split up values of one of the fields (in this case the values of the field Dept)

Dim - Contains the value "Dept" for all the fields.

Similarly, repeat the same steps for the other split up value (Proj) and create another table which would have similar identical fields.

Dept_Proj - The concatenated field

Dim_value - The split up values of one of the fields (in this case the values of the field Proj)

Dim - Contains the value "Proj" for all the fields.

Now, append these two tables, and name it as Dept_Proj_expanded.

Dept_Proj_expanded:

Dept_Proj, Dim_value, Dim

Now, in the data model window, we need to create relations as follows, using the common Dept_Proj field

Fact ---*> Dept_Proj_unique <*--- Dept_Proj_expanded

That is it, now in the UI, create a chart and have Date as the x-axis and the Dim_value from the expanded table as the legend and the Amount as Y-axis.

Make sure to have a slicer which would have the field Dim and always select one of the value. Now, the legend in the chart should change dynamically according to the selection in the slicer. 

This might not work in all the cases, but it is handy whenever we need to change the dimension in the legend on the fly. Please try and let me know if you have any queries.

[Greg_Deckler]

@MichaelP, you can do hierarchies in certain visualizations today in Power BI Desktop. Take a look at adding multiple columns to the "Axis" of a bar or column chart. You will get these little down arrow icons that will let you drill down into the data. Is that what you are looking for?

[jt999]

All true...the problem that I have with this is that you (and EVERYBODY else) has to do this each and every time they want to use a hierarchy. Now thats all fine and good when it is a simple hierarchy. But what happens when it is a complex retail or financial services product hiearchy, with many levels? Now every user has to remember the exact order of every complex hierarchy in the organization...

Defining a hierarchy once so that everyone can simply drag and drop is actually really important from an end user experience point of view

[greggyb]

Hierarchies can currently only be consumed from SSAS. For a time it was (maybe still is?) SSAS MD only.

[MichaelP]

Consumed from SSAS?

I believe we have not the same level of expectations. Created hiearchies from Tabular Model are exploded as separate fields.

Useless.

[mallegrissimo]

Agree with Miclael. 

We have graet demand of custom Hierarchy with simplyness and effciency! it's a MUST HAVE feature!!! VERY DISPOINTED

At least should have the same as in powerPivot.

[djnww]

To everybody on this list, the PowerBI team only released the hierarchy feature from a design point of view to aide with development. It is not intended to work for filtering as yet. This is the next step for the PowerBI team as it is currently under review.

The PowerBI team is very agile and they tend to get ahead of themselves and release an unfinished feature in segments in order to please the community. However this does have drawbacks when the community expect the feature to be the solution to everything. I have been caught out myself thinking a feature is complete when it is only a part rollout.

I guess we can either wait 3 months for a partial solutin that will satisfy some people, or wait 6 months for a full solution with nothing in the interim.

I haven't seen a BI company this agile in development and listening to the community. Yes, they might have a rollout strategy that annoy us, but they are listening.

Daniel

[MichaelP]

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.

[greggyb]

@MichaelP, you've raised several points.

1.  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.
2. 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.
3. 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:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6919488-ability-to-select-multipl...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6709520-drill-down-should-drill-o...

[Rymatt830]

This is good news! I deal with a lot of quarterly data, and not being able to create a year-quarter hierarchy is one of the only big limitations with Power BI Desktop.

[bblais]

Great, thank you for the update!