Forum Discussion
Creating Hierarchies in Power BI Desktop Data Model
- 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.
Since the feature has been started, can you provide a release date for creating hierachies in power bi desktop
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.
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
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?
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
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.
