Microsoft Power BI is a set of robust business analytics visualization tools that has achieved a meteoric rise in popularity, recently passing the 5 million subscriber count since its release as a standalone subscription service in mid-2015. Originally delivered as part of Office 365 in late 2013, it has been projected to replace Microsoft Excel because of its ability to easily create powerful visualizations for reports.
While these visualizations serve a potent and essential role for Power BI, they come with a significant limitation: When Power BI users receive a shared report, the report is in read-only mode by default and cannot be changed dynamically without edit rights. Therefore, end-users are restricted to the singular view presented in the report. This limitation can be a productivity roadblock — for example, if you’re in logistics and want to view a report according to a different dimension or hierarchy to interpret the data with more relevance to your job, you’d have to go into the Power BI dataset to edit the report accordingly.
Power BI really needs a solution that addresses this limitation. While working on a Data & Analytics engagement for an AIM Consulting client, I developed a series of solutions that directly solve for this issue, opening up a new world for Power BI users. The solutions blends a combination of DAX, SQL, scripting and bridge tables to generate dynamic “slicers” that allow users to modify a report without impacting the master dataset. It applies to users of Power BI Pro and Premium versions.
Here’s a description of four use cases affected by this limitation, and how the solution enables users to change their reports dynamically on the fly.
Changing X-Axis “Group by” Values
Imagine a shared data report in your company that shows sales by product. In this hypothetical scenario, the end-user who needs this data for a business function might want to view annual sales as well, or by geography. In order to get this information, you would have to edit the report in the tool, pulling the data from the dataset to make a new report. The solution creates a slicer within the initial report that allows the end-user to adjust the dimension axis of the data on the fly. As you can see, a new list appears for every attribute you can group by.
Changing the Hierarchy AxisThe hierarchy axis in Power BI allows you to clearly see where you are in the data hierarchy as you drill down into the data, but again, you cannot change this without editing the report. The solution creates the hierarchy table dynamically and puts it in a slicer, allowing you to change the hierarchy and drill down on it dynamically, in much the same way as with the x-axis in the example above. Many people don’t think this is even possible, and it took some out-of-the-box thinking to solve for it.
Changing MeasuresMeasures are predetermined values in Power BI. Only pre-selected measures within a visual are seen at the report level when a report is shared. The solution tricks Power BI so that if you select a text value for a measure (such as total sales), you can switch it to the actual total sales measure in that dataset. Or, if you’re looking at month-to-month sales but want to look at product growth month-over-month, you can switch the measure dynamically from sales to order quantity. The solution presents a slicer of all the measures you might want to be able to dynamically switch and solves the problem of how to change the measure on the fly.
Changing VisualizationsPower BI has numerous ways to present data visually in a report, but it’s rare that a particular visualization type in a shared report will solve for all user needs in an organization. To prevent users from having to manipulate the dataset to suit their own needs, this solution presents a slicer for users to select from a set of visualizations. For example, one click changes the view from a Bar Chart to an Area Chart. This solution was created in much the same way as the measures solution.
The Solutions in Action
These solutions provide considerable impact and compelling new uses for Power BI in the enterprise. They save time and frustration at the end-user level from not needing to manually edit every shared report they receive. Also, they can potentially reduce the level of training required for Power BI users as they do not necessarily need to know how to edit a report they receive. These solutions also enable more productivity for users who don’t have edit rights at all as they can change the report on the fly without having to go into powerbi.com to edit the report.
As a Power BI report creator, if you share a report with 200 people in your organization, you certainly don’t want 200 people to alter the original report. With these solutions, all 200 users can change how data is displayed in the report to meet their own needs, enabling a multitude of new routes to data viewing and interpretation within an organization, without having to devote the time and resources to create 200 different reports.
Here’s a look at all of the solutions together in action:
These solutions are available from AIM Consulting and can be tailored to clients’ specific needs to increase the productivity level of Power BI end-users and of your entire organization. They provide compelling new avenues for end-users to view and manage enterprise data in Power BI.
