1200
627
310
310
Read it in 16 minutes
As a consultancy we work with clients and see power bi reports that have been created by other developers that don’t follow best practices. In some cases we have been brought in to complete the project where others started because the project hasn’t gone to plan. This could be internal developers or in some cases external. When we talk of best practice this could be in the power bi report itself or in the power bi project where developers may not approach the project in the right way. Without following best practice this can lead to slow delivery of a project or performance issues with power bi reports.
In this article I wanted to look at some of the examples that we have come across where power bi best practice approach has not been followed and suggest our preferred solution. This article isn’t written as a technical document, it discusses approaches, settings, and features that even the less technical developer can implement. So let’s get started…
To many developers start the development phase with only partial requirements thinking that they will get the requirements as they go along. Understanding the data requirements of the users from the beginning is important. Without all the requirements, how can the development team give the business timelines for deliverables. I’ve also heard on occasions developers say “if I know all the information from the beginning I’d have done it a different way”.
The best way to get the requirements is a discovery session where information, data sources, logic to calculate measures, and deliverables are discussed in one place with all stakeholders there. The users may be non-technical, but they are experts on understanding the data from a business perspective. So, they could say, to derive a particular percentage divide A by B.
Also, in the session discuss ideas for the layout of the reports and the level of detail of any drill down options. For the session to be a success, by the end of the session the developer should have all the information to start the development or have an action plan for any outstanding requirement that couldn’t be gained from the session.
To me I see this as the biggest mistake most developers make. Creating a new power bi file and starting to then build a report without giving the canvas size a second thought is the wrong way of going about it. If you change the canvas size after the report is built will create it’s own problems. Check on the default resolution of the user’s machines. Within most organizations the IT department will have a preferred resolution for all machines. In Power BI the default page size is 720 x 1280px. This is much lower than the resolution of laptops and desktop machines today. Most organization use monitors that have a resolution of 1080 x 1920px and this can be used in Power BI. It still maintains the ratio of 16:9 but gives the canvas more space for visuals and text without scroll bars appearing. Using a better resolution will give the end users a better-looking report.
To alter the page resolution this can be achieved from the format pane of the page.
1) In the type dropdown select “Custom”
2) This will allow you to enter the Height and Width.
3) In the Vertical Alignment either select Top or Middle. I usually leave this set to top.
The most common resolution for business laptops and desktop monitors is currently 1080 x 1920px (HD)
Have you ever started creating the design of the reports without input from the stakeholders. One solution is to create a wireframe template with a mock-up of the visuals and layout. This can be done using an application like Figma, mokkup.ai, or simply in PowerPoint. Whichever application you use, set the resolution to the intended resolution of the reports in power bi. Then export the template and import it into power bi desktop for an exact representation of the agreed mock-up.
I would recommend using the mock-up to get input from the users and once that you as the developer and the users are happy with the output then start work on the power bi report. Some people think that this is just time consuming, but in my experience, it saves time on the project as reports are designed and the layout is right first time.
This image is from Mokkup.ai which is my preferred tool to create wireframe mock-up’s. With this application there’s a free version but whilst evaluating the product I found that there wasn’t enough features unlocked to evaluate it. If you use this application then you need to get the paid version which is a yearly fee.
Within an organization we have seen reports created by different developers that use their own color scheme or formatting. Where this happens there is no standardization, or branding adopted. What colors do you use, What font shall we go for, do you include a logo, are buttons square or have rounded corners? . A better look is for all the reports to have a common theme that also incorporates the corporate colors of the company brand. One of the first tasks that you can do in a power bi project is to get details of the corporate colors and fonts then incorporate these into the reports. Most companies will have details of their branding colors and will insist on using these in the report. Usually these colors would be available from the marketing team who are usually very helpful in assisting in any way. Incorporate the branding colors into the reports will help to keep the leadership team happy as well. This can be easily achieved in power bi by creating a new theme, or modifying an existing theme.
In Power BI desktop application you can change the theme or customize a theme by:
1) Click on the View tab
2) On the ribbon under Themes, click the down arrow.
3) Select the theme you want to customize.
4) Either click the customize current theme, or you can look at creating a new theme.
Theme files are just JSON files so easy to read or modify.
Create a background image for the report is very easy to do, and you don’t need a lot of design skills. Don’t use shapes, lines and images in power bi to make up the page layout. There are so many reports I’ve seen in the past where a developer has applied a background color then added four rectangles with a white fill to create a frame for visuals, then overlayed a logo in the top right corner.
A background image can easily be imported as an image file in the canvas background settings. A lot of people do this using PowerPoint or Adobe Illustrator to create the background color, place some frames around the page for visuals, and finally add a logo image. If you have already created a mock-up template then you know how many visuals are on the page, the size of them, and where to place then on the page.
Tip: Don’t include any page or visual titles in the background image. For accessibility, screen readers will not be able to read the text in the image. We would usually create a frame for these titles but create the text in the power bi report itself. We talk more about accessibility below.
To insert a background image
1) Create an image file to use and save as either png, tif, bmp, jpg.
2) In Power BI open the canvas settings
3) Within the settings open Canvas Background. In here you can browse to the image file previously created.
By default the transparency will be set to 0, set this to 100 for full transparency.
If the report page becomes slow to render then look at the amount of data points on the page. There could be many reasons why a report page is slow to render but one factor could be the amount of visuals on the page and what data is in each visual. For example I’ve seen table or matrix visuals with between 30-40 columns of data. Multiply this by the amount of rows and this is the amount of data that power bi has to process to render the visual. In this instance we redesigned the visual with a high level table containing 8 columns. Then used drill down and drill through features to show the remaining columns. Splitting the visuals up like this improved the performance and the users found it better to navigate.
In power bi we would always recommend using the performance analyzer tool in the first instance to resolving slow performance. But we have found that in a number of cases the amount of data used, or the number of visuals on one page has been the cause of the issue.
We have seen reports within organizations that don’t tell the full story. The users need to go to another application to fully understand the business problem. When designing the report look at it from the eyes of the user. When I design a report, I look at it from a user perspective. Ask yourself the questions: Is it easy for the user to navigate, from the report would the user easily get the answers to the business questions. Does the report allow for easy storytelling.
For example: the sales are low this quarter compared to last quarter, why is that? The answer could be because there are distribution problems getting stock to stores but does the report allow users to understand the Why, What, and How.
If users see any wrong numbers, they will think the whole report is wrong and will lose trust in the report. They will then not use the report and revert to the previous way of working. For this reason, as a developer complete your own testing against the requirements and allow time for this testing. There are occasions where a client has said “Get the report available to users and they can test”. In my opinion this is the wrong approach and would recommend against it. As developers we may get a calculation wrong or format a number incorrectly, but this is where the testing is important. Yes, a small number of users can be involved in the testing process doing UAT (User Acceptance Testing) but the developer needs to do their own testing.
From my experience accessibility is one area that gets overlooked. It doesn’t take long to think about this and add the necessary features. In most cases the customers don’t know that power bi caters for accessibility so including this is a good feature to include in any demo. So lets have a look at some of the common accessibility features.
Color Contrast
Use colors that would not be hard for users with visual difficulties to read. For example, there are many people that are color blind. Best practice would be to steer clear of colors that would be difficult for users like this to read. Also include space around smaller text so that it’s not difficult to read. If you are unsure about particular colors and the contrast between them there is a useful website that you can use: WebAIM: Contrast Checker
Screen Reader
Whilst we are talking of accessibility think about screen readers and how your report will integrate with this accessibility feature.
Alt Text
Include alternative text for images, logo’s, visuals etc. This will help when users hover over the object to understand information about it.
Tip: In the PL-300 (Power BI Data Analyst Associate) exam there were a number of questions on the topic of accessibility.
To create a user-friendly report, it needs to be intuitive, and users will need to easily navigate the report to get the information. I’ve come across reports that are difficult to know where the information is, if you haven’t seen the report before.
Firstly, I like to see an information button on the report that informs the user about the report. If a new user opens the report, I expect them to be able to understand the report reasonably quickly (if they have an understanding of the business data).
I’ve seen a report recently where they needed to report in two different currencies. And to achieve this they had two different power bi files, one in one currency and the other report in the second currency. To address this issue, we created one report with a switch to toggle between the two currencies. From a navigation point of view this is easy to implement with a button for each currency then this controlled the DAX measures used in all the visuals. Also, from a user perspective they don’t have to close one report and open another to see the information in a different currency.
Examples where you can use buttons is to select other pages in the report, clear all filter selections, select different measures, change the visual type etc.
Best practice is to separate the model from the reports. I’ve seen so many instances where multiple reports using similar data has their own model. Let’s say there is financial sales data for an organization, the same model could be used for reports for the finance team, management, and operations. They all may want reports presented in different ways and this is fine. They all can have their own separate reports, but one financial semantic model could be used that incorporate all the financial attributes and measures.
If the model is separated out and published, then multiple reports can use the same model. Don’t recreate the wheel. Also, if in the future there are changes to the model then the changes can be done in one place and all reports are updated.
In the semantic model create a folder structure for the attributes and measures. Have you needed to work on a semantic model that someone else has created. With about 60 attributes and about 30 measures. Having to understand the data can take a while. In power bi you have the ability to create folders and sub-folders. Use this feature to structure the measures and attributes.
In Power BI desktop Model View you have the ability to create a folder structure for measures and attributes. How you structure the folders will be on a case by case basis as the data from one project will be different to another.
Create the folder structure with the Display Folders property.
I always think about it from a user perspective when creating the folders. In a lot of organizations today self service is used a lot where users with the ability can create their own reports. So structure the semantic model how the business users would understand it with meaningful names for the folders. These users may not have a technical background so try not to use any technical terms for folder or attribute names. For columns that the users don’t need to see like primary and foreign keys you have the ability to hide these so not as to confuse the users.
Again to help to structure the data I also prefer to create a dedicated table for measure. Here’s an example.
There are ways to reduce the amount of DAX calculations within your semantic model. Let’s take Year to Date (YTD) DAX calculation. There could be a number of these YTD calculations in your model. This could be sales order amount, Order Qty, Shipped Qty, Margin, and Manufacture Cost etc. In this instance you would have five DAX measures for these that are repetitive and would clutter the model. Where this happens try using calculation groups and have one YTD DAX measure that will replace all the others.
In the above example typically the structure of each YTD measure will look like this. Where in the calculate function, the expression would be the only part that changes. In the example below the expression is the “Sales Order Amount”.
Example Code
YTD Sales Order Amount =
CALCULATE (
[Sales Order Amount],
DATESYTD ( 'Date'[Date] )
)Using Calculation Groups instead use this code:
CALCULATIONITEM 'Time Intelligence'[Calc].”YTD” =
CALCULATE (
SELECTEDMEASURE (),
DATESYTD ( 'Date'[Date] )
)In the expression use the SelectedMeasure() function.
For more information on calculation groups have a look at this article: Calculation Groups – SQLBI
Developers can reduce the amount of visual on in a report by utilizing field parameters. Power BI Field Parameters is a dynamic feature that enables users to switch between different fields in a visual dynamically without needing to create separate visuals for each attribute or measure. It enhances flexibility and interactivity, making reports more adaptable for various analytical needs.
There are many ways to incorporate power bi best practice approach when involved in a project. This is not a full list on best practices just examples that we as a consultancy have come across on live projects with clients. In many of these instances not following best practice could be an internal developer learning power bi and not fully understanding best approaches. On another occasion we completed a project started by another team where the project over ran and the organization was running into performance issues.
The topics mentioned in this article could be and article in it’s own right and in future articles we can talk about some of these in more detail. Explaining how to implement and the best approach to follow for speed of development and to reduce performance issues.
Figma: Figma: The Collaborative Interface Design Tool
Mokkup.ai: Dashboard Wireframing Tool: Design and Present | Mokkup.ai
Color Contrast Checker: WebAIM: Contrast Checker
Contact Our Team
If you are interested in having a conversation to see how data analytics, Power BI, or Microsoft Fabric could help your business. Or if you are already using one of these technologies and want to be more data focused then what are the next steps!
If you are interested in talking to Datahub about any of these topics then our team of consultants are available to understand any challenges that you maybe facing and how one of our solutions could help.
Contact us: Contact us | DataHub Consulting
Datahub Consulting Website: Data Consultancy Services | Datahub Consulting
We do not employ salespeople; our team are all experienced technical specialists that can talk you through any of our services.
Contact us