Rules

Secret ingredients to quality software

Edit
Info

Rules to Better Power BI

15 Rules

If you still need help, visit our Power BI consulting page and book in a consultant.

  1. Do you use version control with Power BI?

    Developers in 2018 have better control history than before.

    Prior to the April 2016 update, storing a Power BI Report in version control could be prohibitive, as pbix files contain the dataset and report definition, which in some cases can be gigabytes in size.

    The April 2016 update features the ability to export a Power BI Report template (pbit) file, which contains the report definition minus the dataset.

    If the pbix file size is not too large, you may choose to store it directly in source control. When a pbix file is very large, then it may be more economical to store the template (pbit file) only in source control.

    Here’s the pros and cons we’ve found for each file type:

    Template pbit files are small, but when you open a template in Power BI Desktop, the dataset needs to reload. If the changes you need to make to a report are small, waiting for a dataset reload can be frustrating (e.g. waiting 10 minutes for the reload to do a one-minute change). The Power BI pbix file will still be required to publish to Power BI Online.

    Power BI pbix files can get very large, but when you open them, the data is there and you can immediately make changes to the report. However, if your workplace is geographically dispersed, then upload and download times to/from source control will be a consideration.

    Having a mixed set of rules for storing different file types based on their size will quickly get messy if you have a number of reports, so make a decision to go one way or the other based on your environment.

    PowerBI SourceControl BadExample
    Figure: Bad Example – Mixed Template and Power BI Files in Source Control

    PowerBI SourceControl GoodExample
    Figure: Good Example – Single File Type in Source Control

    To export a template from Power BI Desktop, select File | Export | Power BI template from the menu, enter a description, file name and click save, as per the below figures.

    PowerBI SourceControl 1 3
    Figure: Exporting a Power BI Template from Power BI Desktop

    Add comments describing the changes made to the report and append to these, in descending order, each time a change is made. This way the history will be at hand each time the template is opened.

    PowerBI SourceControl 2 3
    Figure: Enter a Description for the Template

    PowerBI SourceControl 3 3
    Figure: Enter a File Name and Save

    Save your pbix file to the same folder as the template above, you’ll need these if you want to publish your report to Power BI Online.

    If you’ve decided to store template pbit files in source control, ensure you’ve set your source control application to ignore *.pbix files.

  2. Do you know the best practices around colour?

    Using colour in reports can bring them to life, or else make them confusing and noisy, so make sure you’re intentional with them.

    Semantic colours

    Remember that some colours have a shared understanding of their meaning.

    • Red means pay attention or danger.
    • Green often means good.
    • Traffic lights can be used for showing 3 levels.
    • Cold, Warm, Hot should have appropriate colours that make sense without having to look at the legend

    colours powerbi bad
    Figure: Bad example – Non-semantic colours cause confusion

    colours powerbi good
    Figure: Good example – Obvious colours used for Cold, Warm, and Hot

    Tufte minimalism

    Edward Tufte is often quoted as saying that, on a report where you want to draw the eye to exceptions or out of bounds data, colour should only be used to highlight what you want the user to see.

    tufte good
    Figure: Good Example – The black stands out as everything else looks translucent

    Consistent Palette

    Make sure you stick with one colour palette, so if you use one pastel colour, make them all pastel. If you have one bold colour, make them all bold

    pallete bad
    Figure: Bad Example – Inconsistent palette feel like the colours clash

    pallete good
    Figure: Good Example – Consistent colour palette

    Colour Blind palette

    Just to be difficult, it is worth noting that red/green colour blindness is the most common type, which may make standard semantic colours sometimes less desirable. In this case, use blues and oranges for contrast instead.

  3. Do you avoid using too many decimal places in reports?

    Having decimal places is generally not required when the numbers are there to show a general indication. The only serve to obfuscate trends and quick understanding of the overall information.

    You should generally only include decimal places (especially more than 1) on reports for accountants that will be used for reconciliations.

    powerbi no decimals
    Figure: Good example – having $350.1k would not be useful information. $350k is sufficient

  4. Do you have a single focus number for each report when possible?

    Many reports are expanding on what could be expressed as a single number. When this is the case, make sure that number is clear and bold at the top right of the report.

    Ideally this should be a single number, not a group of numbers. If there is more than one, make sure one is chosen as the main one and put the others in a less prominent colour, size, or position.

    report single number 1
    Figure: One number at the top right sums up the whole report in a single number

    report single number 2
    Figure: Another good example

    report single number 3
    Figure: and another

  5. Do you know how to share links to specific tabs in Power BI reports

    The problem with the standard File | Publish to Web options, is it always goes to the same tab of a report, but if you want to send a link to a specific tab, you have to do one more step...

    Do the above as usual, and then go to that published public report, and navigate to the tab you want to send people to.

    At the bottom right hand corner of the screen, there is a share icon. Click this for a sharable URL for that tab (people can still navigate to other tabs as needed, but will start on the one you've chosen.

    Power BI   Share Tab

  6. Do you know how to add custom branding to the Power BI portal?

    The Power BI Portal can be customized with custom branding. The items that can be changed to make Power BI fit into an organizations brand are:

    • Logo
    • Cover Image
    • Theme Colour

    To make these changes navigate to: Admin Portal | Custom Branding

    Make your changes and save. That's it, you now have a branded Power BI Portal.

    powerbi custom branding
    Figure: Power BI Portal with Custom SSW Branding

  7. Do you place your slicers consistently?

    It's tempting when you create a Power BI report to put slicers wherever they fit, but this can lead to an incosistent experience for users.

    A better solution is to always put your slicers at the top (and towards the right) whenever possible, and also to replace any non-essential slicers with filters instead, as the filter pane fully collapses on the right hand side, and the control to expand it is already at the top right hand corner of the screen.

    PowerBIGroupedFilters
    Figure: Good Example - all the slicers are grouped in a consisdtent location and near the filter bar

  8. Do you know not to use Alphabetical Sorting?

    The default sort order for most visualizations is alphabetical, but you should almost always change this.

    Alphabetical sorting is really only good if you're specifically looking for one item on the X axis, but it's terrible for seeing ranks, trends, or proportions. PowerBI alphabetical

    Figure - Bad Example: Alphabetical gives very little information

    PowerBI-non-aphabetical.png

    Figure - Good Example: Sorting by value shows rank and trends easily

  9. Do you know where to save reports?

    You can save report under My Workspace and share it with your team. This is bad because if you leave your company, your report is gone!

    It's better to save reports under Group Workspace and share it with the team.

    powerbi bad
    Figure: Bad example - saving report under My Workspace

    powerbi good
    Figure: Good example - saving report under Group Workspace

    1. Group work space requires all users to have pro license ($10 / month)
    2. Group work space cannot share report and dashboard via embed link
    3. Until Microsoft fixes PowerBI's group sharing, it's better to use My Workspace with a shared account (i.e. powerbi@northwind.com )
  10. Do you use Email Subscriptions to get the reports you need in your inbox?

    Pro users can quickly subscribe to emails of the report pages that matter most. Once subscribed, Power BI will regularly send screenshots of the report page directly to your inbox.

    Figure: Many people love getting reports in their inbox monthly

  11. Do you know how to integrate Power BI with Reporting Services 2016?

    There are 2 ways to integrate Power BI with SSRS (SQL Reporting Services) that will allow you to be able to find all of your relevant reports from wherever you look for them

    SSRS

    In SSRS 2016, you can list Power BI reports as if they were SSRS ones.

    Power BI Dashboard

    Figure - Good Example: Power BI dashboards can show charts from SSRS reports, and link through to them when clicked.

  12. Do you know the difference between Calculated Columns and Measures in Power BI?

    When you run into a wall in Power BI and feel like you've exhausted the out of the box functionality, that when it's time to investigate what a bit of DAX can do for you. 

    There are 2 different things you can do with DAX, create a Measure or a Calculated Column.

    Calculated columns:

    • Stored in the database
    • Often used to filter/group data

    Measures:

    • Computed on aggregates of values
    • Computed at query time
    • Often used to give a numerical metric
    GroupingColumn = if(value<x, small, if(value<y, medium, large))

    Figure - Good Example: Nested if statements are a great way to split up your data into groups

  13. Do you use the Dataverse connector when connecting the Dynamics 365?

    When creating a Power BI connection to Dynamics 365, the first thing that comes to mind when searching for a connector is to search for Dynamics 365, seems logical enough right?

    dynamics connector 1
    Figure: Bad example - Searching for Dynamics 365 connector

    Wrong.

    When connecting to Dynamics 365 data always use the Dataverse connector (if it is available). Your system admin will need to tinker with some settings to enable this support, but it's simple and easy enough to do, here's the link: https://docs.microsoft.com/en-us/powerapps/maker/data-platform/view-entity-data-power-bi.

    Once enabled instead of using Dynamics 365 (online) connector use the Dataverse connector.

    dynamics connector 2
    Figure: Good example - Use the Dataverse connector instead

    The advantages of using the Dataverse connector are:

    • Supports both Import and Direct Query (Direct Query means live reporting 🙂)
    • Dataverse is built on top of TDS (Tabular Data Stream), meaning it should be much faster than the WebAPI connector
    • Potential to write custom SQL queries for data sources

    The disadvantages of using the Dataverse connector are:

    • None
  14. Do you always choose the right visual for your reports?

    Your reports and dashboards should tell the right story to your end users. They should be able to get the gist of the report at a glance and not have to work at trying to understand what it means.

    Visuals in themselves are neither good nor bad. A visual that users love on one report could be a terrible choice on another report. Power BI comes with quite a few built-in visuals and they are adding more to this all the time. However, sometimes you may need something more than the standard offerings in which case you have 3 options.

    1. Go to AppSource (previously Marketplace) and choose from the many free/paid offerings
    2. Use the Charticulator visual in AppSource to create your own custom visual either using templates or DIY using the UI
    3. Create your own custom visual using custom code with React, Angular, R, etc

    Using the pre-built visuals in AppSource saves time and money and should work in most cases. The Charticulator visual has now been integrated in to AppSource which also gives you more flexibility and options. Creating custom visuals from scratch using D3 or similar libraries can be time-consuming. One relatively easy way to add more visual options to your toolset is to create your own visual using React and then use free 3rd party charts to add the extra sizzle. Check out this video to explore this option. https://www.youtube.com/watch?v=eJ6uHwaGJRM

    For more inspiration on using amazing visuals have a look at some of these urls:

    1. https://community.powerbi.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery
    2. https://flourish.studio/examples/
    3. https://formidable.com/open-source/victory/
    4. https://charticulator.com/gallery/index.html
  15. Do you know when to use SSRS over Power BI?

    Knowing when to use SQL Server Reporting Services (SSRS) over Power BI can ensure that you are using the tools most efficiently to help drive your business.

    Many organizations see Power BI as a replacement to SSRS due to a lack of major advancements in the SSRS space however while similar they both fill different key needs for organisations and the BI team at Microsoft have invested alot of time into improving the SSRS offering.

    SSRS is better for “Pixel perfect” reports that you can design exactly to your specifications, whereas most Power BI visualisations only have a finite amount of visual tinkering possible.

    SSRS provides a stronger ability when it comes to static representational reports, like invoices, monthly reports, mailing lists...

    BI however is better for a dynamic interrogation of data as it currently stands so this can allow power users to drill into live data to identify trends.

We open source. This page is on GitHub