Author: Andrew Walters

Power BI tips & tricks

Posted on by Andrew Walters

To be able to benefit from a truly interactive report personalised with your KPI’s and brand, extract business intelligence and share insights effortlessly, it’s just as important to spend time developing the front end of your Power BI report as it is the data model in the back end. Power BI has many features which can help achieve this and provide a truly interactive experience for the report’s end user.


Designing a Coherent Report: Themes

Themes in Power BI allow you to set a colour and font scheme template for your report. Whether to ensure consistency between a reporting suite, allow easy reading by the visually impaired or to align with your branding, themes allow appropriate design changes to be applied in an instance across a report.

You can either make use of one of Power BI’s built-in themes with predefined colour schemes to do this or you can customise a theme to meet your needs. This customisation ranges from setting page backgrounds and font sizes to setting sentiment colours and the background transparency of a card. To create a truly comprehensive theme, JSON files can be imported where the simplicity of the code can range from setting the theme’s name to specifying that different font families be used for specific visuals.

Not only do themes allow design changes to be applied quickly, but they ensure consistency across reporting, easing the end-user’s navigation through reports.


Unlocking Data: Drill Through and Drill Down

Another key tool which can be used in Power BI to help the user gain a more compressive picture of the data are the Drill Through and Drill Down Functions. Drill Down allows the report user to “Drill Down” to another level of granularity used in a visual’s hierarchy to delve deeper into a period of time or market for example.

To provide the end-user with a truly detailed insight into the data behind the visual, the Drill Through tool can be used. This allows the report designer to take the user from one visual of a report to another page to focus on a specific article. On this page, the report designer can create multiple views with different measures focusing on the one article.


Creating An App-like Report: Buttons and Bookmarking

Buttons and bookmarks are another incredibly useful technique in enhancing user interactivity with a Power BI report. Buttons can be used simply to allow the user to quickly navigate between the different report pages but can be further used with the help of bookmarking to allow the user to quickly switch between different trends and visuals within the same location of a page. They can be further used to incorporate information pop-ups into your report and allow you to create in depth filter panes.

The use of buttons and bookmarks allows you to create a report with the functionality and usability of an app, eliminating the need for cluttered report pages and facilitating rapid navigation between visuals.


Scenario Modelling: What-if Parameters

Another great tool to improve a report’s interactivity are What-if Parameters. What-if Parameters allow you to model different scenarios and view the influence of certain parameters on your KPIs.

To do this a new What-if Parameter can be created, where a data type and range of numbers is specified. This creates a single column for your range of values which can be placed onto a slicer as well as a selected value measure which can be used as part of a larger DAX expression to model different scenarios.

This tool helps the user to determine the impact of particular parameters on KPIs, helping them to make proactive business decisions.


Enhancing Interactions: Interactions Between Visuals

By default, interactions between visuals in Power BI are enabled. These allow page visuals to be filtered based on an interaction with another visual on the same page. While this interaction is useful to compare different visuals it can create confusion on larger report pages with many visuals. Therefore, only enabling filters to interact with visuals and only enabling interactions between visuals in close proximity (or not at all based on preference) would be a simple technique to improve report usability.


Visual Selection

Even the choice of visual can have a great impact on how data is perceived. For example, if many data points are going to be applied to a visual, a stacked column chart should be considered as opposed to a line chart. A line chart could create a confusing picture with multiple lines making it difficult for the user to compare data points, whereas a stacked column chart will allow easy interpretation of figures in this instance.


Conclusion

By adopting some of the many reporting features that Power BI has to offer, it’s possible to create a truly interactive and coherent report, facilitating a connection between data and decision making.


References

5 IDEAS to take Power BI reports to the NEXT LEVEL – YouTube

Can What If parameters help in Power BI reports? – YouTube

Set up drillthrough in Power BI reports – Power BI | Microsoft Docs

Use report themes in Power BI Desktop – Power BI | Microsoft Docs

Power BI – Edit Interactions features – Power BI Docs

Four key features of Power BI

Posted on by Andrew Walters

Power BI is one of the leading data visualisation tools providing quick insights into a company’s performance and allowing data-driven business decisions to be made. 

Power BI allows users to collate data from multiple sources and present it in the form of interactive reports accessible through a self-service method. However, the functionality of these reports depends greatly on decisions taken in the data modelling stage of the report’s construction and these decisions will have a significant impact on the report’s efficacy.


New call-to-action

Here are 4 features you should be aware of:

 

1. Benefits of Calculation Groups

Since the introduction of the Tabular Editor Add-In, Calculation Groups can be utilised to eliminate the use of excess measures in your report. Instead of needing to create similar intelligence across multiple KPI’s, a single grouped one can be made. Therefore, to see YoY, WoW and MoM metrics for revenue and sales one “Calculation Group” containing three measures for this time-based intelligence can be substituted in place of six individual measures. Not only does this clean up your report by reducing the number of fields contained but it also improves performance.

 

2. Future-proofing your reports

Aggregating data is also key to creating a robust, future-proof model. By only aggregating your dataset to the granularity you require in your report, redundant data isn’t pulled, reducing the model size as well as speeding up data refreshes. The benefits of aggregations may not be obvious at first but will be noticeable over time as data accrues and your model grows.

Being scrupulous is key to improving reporting performance. You should go further than aggregating your data by removing unnecessary columns from your data model, especially those with many unique values. This will improve data compression and thus improve performance. Furthermore, any unnecessary Applied Steps in Power Query should be omitted. For example, if you’ve previously sorted your data in Power Query to help you inspect it, remove this step to be certain that Power BI isn’t performing any superfluous tasks.

 

3. Utilising defaults

As default, Auto Date / Time is enabled in Power BI. Although useful in providing support to build date hierarchies, hidden date tables are created for each date field in your model to do this. A more efficient use of Power BI resources would involve bringing your own date table into your model (imported or created using DAX) and disabling Auto Date / Time. This would allow you to handle your date filtering requirements without your model making inefficient use of resources.

 

4. Optimising DAX Expression Performance

Even the way in which you calculate your KPI’s can affect your report’s performance. Therefore, it’s vital to maximise the efficiency of DAX expressions which can be achieved by declaring variables. Instead of repeating calculations within an expression, a variable can be declared to define this functionality and can then be called upon later in the expression. For example, in a YoY calculation the best practice would be to declare your prior year calculation as a variable rather than to calculate it twice within the YoY expression. This can drastically reduce the query time as expression logic isn’t unnecessarily repeated, rather a metric is calculated once and later just referred to.

As well as improving query performance, the use of variables helps to improve an expression’s readability which will prove beneficial if the expression needs to be debugged at a later stage, with expression logic able to be checked individually rather than in a nested format.

 

Conclusion

A robust and efficient data model is the foundation of any good Power BI reporting solution. The above techniques are just some of the many best practices Power BI has to offer for improved reporting performance. By deploying your selection of Power BI’s many tools to optimise report efficiency and usability, you can achieve the optimum reporting solution for your needs.

Data-driven decision-making, made easy with Ipsos Jarmany

About Ipsos Jarmany

Ipsos Jarmany is a team of expert Power BI consultants based in London, UK. Contact us to discuss how we can help you visualise your data through Power BI.

 
 

References

REDUCE the # of measures with Calculation Groups In Power BI – YouTube
The How and Why of Power BI Aggregations – YouTube
A comprehensive guide to Power BI performance tuning – SQLGene Training
2 ways to reduce your Power BI dataset size and speed up refresh – YouTube
Power BI variables with efficiency and debugging – Learn DAX
DAX: Use variables to improve your formulas – Power BI | Microsoft Docs