Beginners Guide to Power BI Best Practices

Keen to learn more about Power BI? Want to make the most of your Power BI journey? Wondering what best practices to follow? Then, you’ve come to the right place, this Blog Post was made just for you! The focus of this blog is to function as a quick guide to understanding the various steps that go into creating a Dashboard or Report when using Power BI. If you want to learn more about a specific area follow the links in this post to our insightful blog posts on all things Power BI.

This blog covers the following 5 topics:

    1. Connecting and Shaping your data
    2. Data Modelling
    3. Calculated Columns and Measures
    4. Speed and performance
    5. Best practice for data visualization.

       1. CONNECT and SHAPE DATA

      Before you load the data into Power BI, make sure that you are properly organised;

Create intuitive table names from the get-go. Your table names should not have spaces. (If you’ve referenced the tables in various places, then doing an update, later on, will prove to be quite the challenge.) Power BI Table naming convention no spaces

  • Set up a logical folder structure right from the start.
    In other words, you need to create proper file names, and established locations. This will help so that you don’t need to change any data source settings later on.
  • Enable refresh for tables that will change but disable report refreshes for any fixed sources. Remember, you don’t need to regularly refresh sources that don’t update at all or infrequently. Examples of these are lookups or static data tables.
  • Always, only load the data that you need, especially when you work with large tables.
    Too much, or unnecessary data will affect the speed and performance of the report.
    When you only need daily data, you don’t need to include hourly data.
    And when you are trying to obtain insights regarding store-level performance, you don’t need product-level transactions.

        2. Best Practices for DATA MODELLING

      Make sure that you build a normalized model from the start.

  • Ensure that each table in your model fulfills a distinct purpose.
  • Instead of using merged tables, rather use relationships. Long and narrow tables are better than short and wide tables.
  • Within the Diagram view, it’s better to place Lookup tables above Data tables in the diagram view.

Data flow power bi relationships

This serves as a visual reminder that filters flow downstream.

  • Don’t implement complicated cross-filtering, unless necessary.
  • In order to prevent an invalid filter context, it’s best to hide fields from the report view.
  • The best practice is to hide foreign keys so that users access the correct and necessary fields

 

For an in-depth explanation of the above visit the following two blog posts:

(1) ‘Data Relationships in Power BI’

https://databear.com/relationships-power-bi/

(2)  ‘Star Schema Power BI’:

https://databear.com/data-preparation-part-6-star-schema-power-bi/

 

    3. CALCULATED COLUMNS & MEASURES

      Only use a calculated column when a measure won’t suffice.

We will give a short summary of things to take into account below.

  • When you want to “fix” static,  values to each row in a table, then a calculated column will do the trick.
  • With aggregation or dynamic values, it is best to use measures.
  • As a best-practice, it is best to write measures for even the most basic calculations such as Sum of Sales.
    A measure can always be used in other places within your report.
    Measures can also be used as an input into other, more complicated calculations.
    It’s best not to have implicit measures.
  • With DAX it is best to start with simple parts first, before moving to more advanced formulas.
  • When referencing columns, use the table names and measures only.
    As such, use names that’s easier to read and also more intuitive and differentiates them from measure references. For example :  (‘CustomerX_Sales Data'[Invoices Quantity])

 

For a detailed article on Calculated Columns & Measures please visit our insightful post on this topic below:

https://databear.com/calculated-columns-and-measures/

        4. SPEED & PERFORMANCE
  • Remove unnecessary columns. (remember narrow, but longer data tables are better than wide and short tables).
  • Very important: Data tables should preferably only contain foreign keys and quantitative values.
    Additional descriptive columns can be housed in related lookup tables.
  • Imported columns are preferred to Calculated Columns.
    (Calculated Columns increase file size and use RAM).
    Calculated columns need to cycle through each row, and are therefore time-consuming.
    Additionally, it also takes a lot of processing power.
  • As a best-practice, create calculated columns in your raw database or within the Query Editor.
    This is better than having to process those calculations in the data model.
  • Try to use iterator functions such as SUMX sparingly, because these functions will iterate through every row and do the calculation.

 

For more information on the basics of importing data and how that affects speed and performance please visit the link below.

https://databear.com/data-preparation-part-1-the-basics/

     5. BEST PRACTICES FOR DATA VISUALIZATION

       Keep it Simple and Clear

  • Do not over clutter, but have an insightful story.
  • Make sure the report is functionally easy to use and designed neatly.
  • Your data must have context.
  • In order to draw robust insights and conclusions from your data, you can make use of;
    filters, bookmarks, and effective visualizations.

 

In Conclusion, when thinking about your Power BI report:

There is 3 primary question you can ask yourself:

     (1)  Is my data, integers, categorical, geo-spatial, time-series, etc?

  • That is to say, you need to identify the type of data that you are looking at.

     (2)  Am I analysing relationships, trends, compositions, etc?

  • In other words, establish a narrative that you want to follow.

     (3)  Is this report for an Analyst, CEO, sales rep, etc?

  •  Understand your consumer.

That is all from us today. Hope this was insightful and a good refresher on building your Power BI reports. These small steps can make a big difference in the way you build your Power BI reports.

For additional information on Best practices for Building Visuals in Power BI Desktop you can also follow this link:

https://powerbi.microsoft.com/en-us/blog/best-practices-building-visuals-in-power-bi-desktop/