Tuesday, November 17, 2015

Can we use user-defined hierarchies in the model with Power BI?

Power BI is becoming richer and richer with its functionalities and will be surly the best in terms of BI client tools. Since it is still being completed, some important functionalities are still missing and sometime it makes us very uncomfortable to use it. One of the key object we build with models is hierarchy, that is very usable when navigating via a set defined with levels using dimension attributes. This navigation is called as Drilling Down. Most common and famous example is, a hierarchy on date dimension that starts from Year, then Semester, Quarter, Month and Date. Power BI does not recognize these user-defined hierarchies defined in models, hence drilling down was initially impossible. I believe that this is being addressed and it will be available in future releases, until that a workaround is given.

If you have installed AsdventureWorks Tabular Model, it has some hierarchies defined. The following shows a hierarchy defined with Date dimension.

And if you connect with this source using live connection;

And expand date dimension, you will not see the hierarchy.

However, latest version of Power BI allows you to configure a hierarchy within the chart. All you have to do is, drag and drop attributes to Axis box, forming a hierarchy. The following shows how it looks like when it is added.

Now drilling-down is possible with the chart. It is not enabled by default, the icon in the right-top has to be used for enabling it. Once enabled, you can click on the bar, example, bar that represents 2013 and go into semesters related to it. For drilling-up, the icon in the left-top has to be used.

This works fine without any issue and this is the way of implementing drilling-down until we get a version that supports user-defined hierarchies in models.

No comments: