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.
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 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.