Identifying performance problems

Intermediate Data Modeling in Power BI

Sara Billen

Curriculum Manager at DataCamp

Resolving performance problems

An image of a woman sitting behind a computer screen where a dashboard is loading slowly.

Intermediate Data Modeling in Power BI

Performance problems

Drawing of an hour glass.

Where things can go wrong:

  • Data import
  • Querying the database with DirectQuery
  • Displaying visuals
  • Calculated versus computed columns
  • Inefficient relationships
    • Many-to-many relationships
    • Bi-directional cross-filtering
Intermediate Data Modeling in Power BI

Optimizing data import

  • Remove unnecessary rows and columns
  • Choose correct data types
    • Numeric data takes less space
    • Casting and aggregating data is slower
  • Group and summarize data
    • Store less data on disk
    • Get to aggregate results faster

Diagram of the different ways to import data in Pozer BI; Excal, CSV, and a database.

Intermediate Data Modeling in Power BI

Optimizing Direct Query

  • Two ways to connect to data:
    • Import model: stores data in Power BI
    • Direct Query: directly queries the database

$$

  • Limit parallel queries
  • Relational database advice
    • Write efficient SQL queries
    • Use appropriate indexes
    • Get the right columns and rows

Diagram of the different ways to load data in Power BI. Import functionality on the one hand and Direct Query (connected to a database) on the other.

Intermediate Data Modeling in Power BI

Calculated versus computed columns

Build custom columns with:

Calculated columns Computed columns
DAX Power Query (M)
Fast for simple calculations Fast for simple calculations
Slow for complex calculations Fast for complex calculations
Generated per visual at runtime Generated once at import time
Intermediate Data Modeling in Power BI

Removing bi-directional filtering using filter measures

  • Use case for bi-directional filtering
    • Find relevant slicer entries between dimensions
  • We can create filter measures to avoid bi-directional relationships for the third use case!
Intermediate Data Modeling in Power BI

Removing bi-directional filtering using filter measures

Data model in Power BI showing only single direction filters and the Slicer_MyFactTable filter measure.

Intermediate Data Modeling in Power BI

Removing bi-directional filtering using filter measures

1) Create a filter measure in DAX:

Slicer_MyFactTable = INT(NOT ISEMPTY('My Fact Table'))
  • Returns 1 if at least one value in the fact table
  • Returns 0 if no values in the fact table

$$

2) Add a visual filter to the slicer and set where Slicer_MyFactTable = 1

Intermediate Data Modeling in Power BI

Displaying visuals

Computer screen showing a report in the Power BI application.

$$

  • Use restrictive filters to minimize data
  • Show as little data as possible on visuals
  • Limit the number of visuals on report pages
  • Use only fast custom visuals
Intermediate Data Modeling in Power BI

Let's practice!

Intermediate Data Modeling in Power BI

Preparing Video For Download...