Adatis BI Blogs

Hierarchy Navigation In Power BI

Until recently, the overall functionality of the Matrix Visual in Power BI has been limited. However, this all changed when Microsoft announced the Power BI March update, which gives users access to the Matrix Preview. This can currently be used alongside the old matrix. In this blog, I will be comparing the old Matrix and the new Matrix Preview. The updates featured in the latter are as follows: Performance improvements Drilling into hierarchies Stepped layout Cross highlighting and filtering from matrix headers and cell contents This article will only focus on the drilling into hierarchies’ functionality. Click here to find more information around the Matrix updates, along with the extra features not covered in the blog. Hierarchy Drill Through One of the visible changes in the new matrix preview it is the ability to show all the headers collapsed by default making the user experience easier when dealing with large datasets. The image below shows this new feature compared to the old and new Matrix. There is also the capability to show both levels of the hierarchy simultaneously , which is again done using the hierarchy navigation buttons as illustrated in the image below. You can also drill up and down on individual columns using the right click function as shown in the image below. The benefit of this is that it gives the user a more detailed drill down of a desired column. Further drill down options are available, for example, the ability to drill down on row category headers. In normal mode (without drill mode turned on), other datasets in other row category headers will be faintly visible. By turning on the drill down mode it allows users to works on a specific category row header in isolation. The following images show the differences in the two views. Conclusion The Matrix Preview has brought about interesting and useful tools making it more interactive. The ability to be able to drill up and down within a report particularly stands out for me. It is also worth mentioning that other features, not covered in this blog give users increased customisation when working on reports – showing how impressive the Matrix Preview is.  April`s Power BI update includes more features for the Matrix Preview. My next blog will be looking at the following two features added for Matrix Preview: Rename axis titles New matrix visual enhancements: column sorting, column resizing, and word wrap   Further Reading (Power BI Blogs)

Maintaining Hierarchy State On Report Refresh

I recently had a request from a client who wanted to display use a parent child hierarchy to navigate their report. The issue with this however is when you click the hierarchy the report refreshes, the data is updated in the charts, but the hierarchy collapses to its original state as below. This wasn’t suitable for the clients needs as it was a large hierarchy with several levels of depth.   What was required was to maintain the state of the hierarchy so it would stay expanded during report refresh. After a little thought I came up with the following solution: 1. Setup a standard report with an indented hierarchy with expandable sections. This has been covered in many places so I’ll only give a brief overview, for this example report I have used adventure works multidimensional model with the following MDX query as the source for the hierarchy data:- Add the fields in a matrix like this:   Right click the Organizations text box, select ‘Textbox Properties’ and in the Alignment tab set the Left padding property to the following value (change the number to adjust indentation): Following this you need to setup groupings in order for the expanding hierarchy. Add a grouping on the UniqueName property On the visibility section set the toggle property to HIDE (ignore the expression for now -) and set the "’Display can be toggled by this report item” property as per the below. In the advanced section set the recursive parent property to [Organisations.ParentUniqueName] and this should give a working report with an expandable hierarchy.   2. Add a hidden parameter to the report called OrganisationLevel. Set the default value to the top of the hierarchy. Add another column to the matrix, add text to it for the link. Select the text and right-click the selected text navigate to properties and place an action to go to original report and pass the OrganisationLevel parameter with a value of [Organisations.UniqueName]   3. Add a dataset dsHierarchyVisibleMembers with the below MDX query – this query uses the OrganisationLevel parameter to work out which level in the hierarchy the user has clicked on and the entities at the same level which we can then use to control the state of the expanding hierarchy.    Now set the visibility property on the row grouping to the following value: The last step is to set the hierarchy collapsed/expanded state, which we using the following formula for the ‘InitialToggleState’ property of the text box to the following. Once these steps are completed clicking one of the elements in the report will pass the parameter, but the hierarchy state is maintained between clicks. Clicking View on ‘Northeast Division’ the left report displays the following in the right – note the chart has been updated but that the hierarchy expansion state remains as it was before the user clicked.   That’s all for now, hope this is of use.