Power BI Performance Tuning: Make Reports Faster

A slow Power BI report is rarely slow for one reason, and guessing wastes days. Microsoft's optimization guide lists the layers where time is lost: the data source, the model, the visuals and the environment, meaning capacity, gateways and network (Microsoft Learn: optimization guide). The reliable method is a loop: measure, fix the biggest cost, measure again.

Step 1: measure with Performance Analyzer

In Power BI Desktop, open the Optimize ribbon and select Performance Analyzer; in the service, it is on the View menu when editing a report (Microsoft Learn: Performance Analyzer). Select Start recording, then Refresh visuals to time every visual on the page, or interact with slicers to time the interactions users complain about. Each visual's time is split into categories:

Two details matter when reading the numbers. Durations include time spent queued while other operations complete, because most operations share a single user interface thread, so a large Other value often means the page has too many visuals rather than that one is slow. And Copy query gives the exact DAX a visual sends, which you can run in DAX query view to test a rewritten measure in isolation. Export saves the session as a JSON file, useful for comparing before and after.

Measure more than once, and record the same interactions each time, before drawing conclusions.

Step 2: shrink the import model

A smaller model refreshes faster and usually queries faster. The VertiPaq engine compresses imported data, often around ten times, but Microsoft still recommends loading as little as possible (Microsoft Learn: data reduction):

• Remove unnecessary columns. Keep only columns used for reporting or for model structure such as relationships and security. Adding a column later is easier than removing one that reports depend on.
• Remove unnecessary rows. Load only the history reports need, with a Power Query parameter such as the last five years.
• Group and summarize when detail is not needed; aggregations for large datasets shows how to keep detail available at the same time.
• Prefer Power Query custom columns to DAX calculated columns, which typically compress less well and are built after all tables load.
• Disable load for helper queries that only feed other queries.
• Turn off auto date/time, which creates a hidden date table for every date column, and use one proper date table instead.

Step 3: reduce column cardinality

Text and other non-numeric columns use hash encoding: the engine assigns an identifier to every unique value. So the number of distinct values in a column, its cardinality, drives much of its cost, and high-cardinality columns are the first place to look. The UCI Online Retail dataset, a year of a UK online retailer's transactions (UCI: Online Retail), shows how much a simple split can change:

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    sales = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
print(f"{len(sales)} rows")

when = sales["InvoiceDate"]
parts = sales["InvoiceNo"].str.extract(r"^([A-Z]*)(\d+)$")      # letter prefix, number
columns = {
    "InvoiceDate (date and time)": when,
    "  date part only": when.dt.date,
    "  time part only (to the minute)": when.dt.strftime("%H:%M"),
    "InvoiceNo (text)": sales["InvoiceNo"],
    "  number part, as a whole number": parts[1].astype("int64"),
    "  letter prefix (C, A or none)": parts[0],
    "Description": sales["Description"],
    "CustomerID": sales["CustomerID"],
    "Country": sales["Country"],
}
for name, col in columns.items():
    print(f"{name:<34} {col.nunique():>6} distinct")

541909 rows
InvoiceDate (date and time)         23260 distinct
  date part only                      305 distinct
  time part only (to the minute)      774 distinct
InvoiceNo (text)                    25900 distinct
  number part, as a whole number    25900 distinct
  letter prefix (C, A or none)          3 distinct
Description                          4223 distinct
CustomerID                           4372 distinct
Country                                38 distinct

The date-time column has 23,260 distinct values. Split into a date column and a time column, the parts have 305 and 774, and if reports never analyse time of day, the time column can go entirely. The date column is also the one that relates to a date table. The invoice number is the pattern Microsoft describes for order numbers with a text prefix: here the letter marks cancellations, so it moves to its own three-value column and the rest becomes a whole number, which uses the more efficient numeric encoding. These are counts of distinct values, not measured model sizes; confirm the effect by comparing file size or refresh time before and after.

Step 4: write lighter DAX

When DAX query time dominates, copy the query and work on the measure:

• Store repeated expressions in variables, so they are evaluated once; DAX variables shows the pattern.
• Use simple Boolean filters in CALCULATE where a column condition is enough, and keep FILTER over whole tables for conditions that need it.
• Iterate the smallest table that gives the right answer, rather than the whole fact table.
• Avoid bidirectional relationships unless a design needs them, since they add filter propagation work to queries.

Step 5: lighten the pages

• Apply the most restrictive filters. Microsoft's example is a table over more than 100 million rows that users always slice down to a few dozen; a Top N filter capping it at 10,000 items leaves the experience unchanged and cuts memory use sharply.
• Limit visuals per page. Move detail to drillthrough pages and report page tooltips instead of crowding the canvas.
• Test custom visuals, since a poorly optimised one can slow the whole report; custom visuals covers how to choose them.

Step 6: check storage mode and the environment

DirectQuery reports depend on the source, so slow Direct query timings are fixed in the database; composite models and DirectQuery covers mixing modes. Beyond the report, check capacity settings, size gateways for their workload, and keep data sources, gateways and capacity in the same region where possible, because every request crosses the network. Dashboards help too: tiles are served from a cache, so a dashboard of the most requested visuals can serve as a fast first page.

Performance optimization is part of the advanced Power BI concepts module of the Power BI course, and dashboard optimization is covered in its dashboard design module.

Sources

• Use Performance Analyzer to examine report performance (Microsoft Learn) — Microsoft Learn
• Data reduction techniques for Import modeling (Microsoft Learn) — Microsoft Learn
• Optimization guide for Power BI (Microsoft Learn) — Microsoft Learn
• Online Retail dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository