Leveraging Microsoft Power BI across your organization requires a solution to the service’s viewing limitations.
While working on a Data & Analytics engagement for an AIM Consulting client, we developed a series of solutions that directly solve the viewing limitation 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.
What is Microsoft Power BI?
Microsoft Power BI is a set of robust business analytics visualization tools that offer the capability to easily create powerful, visually immersive, and interactive visualizations for your organization’s reports.
Power BI was the leading business intelligence software in 2021 with more than one-third of the market share.
What are the limitations of Microsoft Power BI?
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.
How to Solve Viewing Limitations for Microsoft Power BI
We cover four use cases affected by this viewing limitation for Power BI and how the solution enables users to change their reports dynamically on the fly:
- Changing X-Axis “Group by” Values
- Changing the Hierarchy Axis
- Changing Measures
- Changing Visualizations
1. Changing X-Axis “Group by” Values in Power BI
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.
2. Changing the Hierarchy Axis in Power BI
The 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.
3. Changing Measures in Power BI
Measures 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.
4. Changing Visualizations in Power BI
Power 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.
Benefits of the Power BI Report View Solution
These solutions provide considerable impact and compelling new uses for Power BI in the enterprise. Benefits include:
- Saving time and frustration at the end-user level by not needing to manually edit every shared report they receive
- Can 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
- Enabling more productivity for users who don’t have edit rights, as they can change the report on the fly without having to go into powerbi.com to edit the report
The Power BI Solutions in Action
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:
Need Help with Power BI?
AIM Consulting offers robust Power BI solutions which can be tailored to clients’ specific needs to increase the productivity level of Power BI end-users and of your entire organization.