Skip to content
FR

Optimizing Model Performance

Step 13 of 24

Optimizing Model Performance

Introduction

This lesson closes the domain Model the data (25–30% of the PL-300 skills measured, outline dated April 20, 2026). The official outline lists four skills under "Optimize model performance": remove unnecessary rows and columns, identify poorly performing measures, relationships and visuals by using Performance Analyzer and the DAX query view, and improve performance by reducing granularity. Questions describe a slow report or an oversized model and ask which action gives the best improvement, or which tool reveals the cause. Microsoft does not publish the question count or the mix of question types; OnLevelUp mock exams use a 50-question practice length and an 80% practice target, neither of which is official or a conversion of the 700/1000 passing score.

After this lesson you will be able to explain what makes an Import model large or slow, shrink a model, read Performance Analyzer results, run a visual's query in DAX query view, and choose between reducing cardinality, summarizing data and using aggregations.

Learning objectives

  • Explain how the VertiPaq engine compresses columns and why cardinality drives model size.
  • Remove unnecessary rows, columns and tables, and avoid auto date/time overhead.
  • Choose better data types and split high-cardinality columns.
  • Use Performance Analyzer to separate DAX query time, visual display time and other time.
  • Run and edit a visual's DAX query in DAX query view and test a measure rewrite.
  • Reduce granularity by summarizing fact tables, and know when to use aggregations or mixed storage modes.

1. Why models are slow: how Import mode stores data

In Import mode, Power BI stores each table in the in-memory VertiPaq engine, column by column. Each column is compressed using dictionaries (a list of the distinct values), run-length encoding and value encoding. Three consequences matter for the exam:

  1. Columns, not rows, are the unit of cost. A column with few distinct values (Country, Year) compresses extremely well. A column with millions of distinct values (a GUID, a timestamp with seconds, a free-text comment) compresses badly and dominates the model size.
  2. Cardinality (the number of distinct values in a column) is the main driver of size and of query cost.
  3. Calculated columns are stored in the model and increase its size, whereas measures are computed at query time and take no storage.

Slow reports have three typical causes: a model that is too big (long refresh, high memory), DAX measures that are expensive, and visuals that issue too many or too heavy queries.

Symptom Likely cause First action
File size very large, refresh slow Unneeded columns, high-cardinality columns, too many rows Remove columns/rows, reduce granularity
One visual slow, others fast Expensive measure or visual with thousands of points Performance Analyzer, DAX query view
Every visual slow Poor model design (many-to-many, bidirectional filters, wide flat table) Star schema, single-direction relationships
Report slow on DirectQuery Source database slow or poorly indexed Optimize source, aggregations, Import for dimensions

2. Remove unnecessary rows and columns

The cheapest data is the data you never load.

Remove columns that no visual, measure, relationship, row-level security rule or filter uses. In Power Query, use Choose columns or Remove columns, preferably early in the query so that query folding pushes the projection to the source. Typical removals: technical keys not used in relationships, audit columns (CreatedBy, ModifiedAt), description fields, duplicated text versions of codes.

Remove rows you do not need: cancelled test orders, years outside the business need, other regions. Filter in Power Query (Table.SelectRows), ideally with a step that folds to the source, or use a WHERE clause in a native query. Another way to limit data is to apply incremental refresh with RangeStart and RangeEnd parameters, which keeps recent partitions fresh and avoids reloading history.

Disable load for staging queries (right-click → uncheck Enable load) as seen in the Power Query lesson.

Auto date/time. When enabled, Power BI creates a hidden date table for every date column, which increases the model size. The recommended practice is to disable it (File → Options → Data Load) and use one shared date table.

Data types. Use whole numbers instead of decimals when no fractions are required (a fixed decimal number is also cheaper than a floating-point decimal for currency). Never store numbers or dates as text. Round values you do not need to the precision you do (a latitude with 12 decimals needs 4).

Hide, don't delete? Hiding a column in Report view does not reduce its size, because the column is still stored. Only removing it from the model does.

Worked example: shrinking a 900 MB model

Scenario. The sales model is 900 MB. FactSales has 40 million rows and these columns: OrderID, OrderTimestamp (date and time to the second), ProductKey, CustomerKey, Quantity, Amount (decimal with 6 digits), Comment (free text), CreatedBy, RowGuid. Reports use only monthly and daily sales, by product and customer.

Step 1 – find the heaviest columns. In DAX Studio or Tabular Editor with VertiPaq Analyzer, you would see RowGuid, Comment and OrderTimestamp as the biggest columns because of their cardinality. Without these tools, reason about distinct values: a GUID is unique per row; the timestamp has up to 86,400 values per day.

Step 2 – remove what is unused. Delete RowGuid, Comment, CreatedBy in Power Query. They are used by no visual, measure or relationship.

Step 3 – split the timestamp. Reports need only the date, so create OrderDate (type Date) and drop the time part, relating OrderDate to the date table. If the time of day is needed, keep it as a separate low-cardinality column (hour of day), not as a seconds-level timestamp.

Step 4 – fix types. Round Amount to 2 decimals and use Fixed decimal number.

Step 5 – reduce granularity if allowed. If no report needs individual order lines, group by date, product and customer, which could cut 40 million rows to a few million (see section 5).

Why other choices are worse. Hiding the columns leaves them stored. Switching to DirectQuery avoids the size but usually makes every visual slower. Buying more capacity treats the symptom, not the cause.

3. Performance Analyzer

Performance Analyzer is a pane in Power BI Desktop (Optimize ribbon → Performance analyzer; also available in the Power BI service in edit mode). It records the time taken to update each visual after a user action.

How to use it:

  1. Open the report page, select Start recording.
  2. Select Refresh visuals (or interact: change a slicer, open a page).
  3. Read the results: for each visual, the time is split into:
    • DAX query: time for the semantic model to return data. Long values point to expensive measures, relationships or model design.
    • Visual display: time to render the visual. Long values point to visuals with too many data points or complex custom visuals.
    • Other: time waiting for other visuals' queries, background work.
  4. Use Copy query to copy the DAX query for a visual, or Run in DAX query view to open it directly in DAX query view.
  5. Use Export to save the results as JSON.

Typical conclusions:

Observation Meaning Action
High DAX query, low display Measure or model is slow Test the measure in DAX query view, review relationships
High display, low DAX Visual draws too many marks Reduce points, change visual type, use filters or top N
Many visuals on a page, all slow Page too heavy; queries compete Fewer visuals, bookmarks or drillthrough pages, Apply all slicers
First load slow, later fast Cold cache Test with cleared cache for realistic timing

Tip: start recording, clear the cache by closing and reopening the report in a representative test, and compare before and after a change; one measurement is not a benchmark.

Other report-side practices: limit the number of visuals per page; avoid visuals with thousands of categories on an axis; use "Apply" buttons for slicers (Query reduction settings) so that the report does not query on every selection; prefer slicers on low-cardinality columns; and use drillthrough instead of a huge detailed table on the main page.

4. DAX query view

DAX query view (a view in Power BI Desktop, next to Report, Table and Model views) lets you write and run DAX queries that start with EVALUATE against the semantic model, without leaving Desktop. You can:

  • run any query, including those copied from Performance Analyzer;
  • use Quick queries (right-click a table, column or measure) to generate queries such as showing the top 100 rows of a table, or a column's statistics;
  • define measures in the query with DEFINE MEASURE, test them, and then update the model with changes to push them into the semantic model;
  • view results in a grid and compare timings.
DEFINE
    MEASURE FactSales[Sales Test] =
        SUMX ( FactSales, FactSales[Quantity] * FactSales[UnitPrice] )
EVALUATE
SUMMARIZECOLUMNS (
    DimDate[Year],
    DimProduct[Category],
    "Sales", [Sales Test]
)
ORDER BY DimDate[Year], DimProduct[Category]

Typical workflow for a slow visual: copy its query from Performance Analyzer into DAX query view, run it and note the time, rewrite the measure (for example, replace a FILTER ( table, … ) over the whole fact with a Boolean filter argument in CALCULATE, or store an intermediate result in a variable), run again and compare, then update the model. DAX query view also displays a result grid limited to a maximum number of values, so it is for diagnosis, not for exporting data.

Measure-level optimizations the exam may mention:

Slow pattern Better pattern Why
FILTER ( ALL ( Sales ), Sales[Color] = "Red" ) as a filter Sales[Color] = "Red" Boolean argument Filters one column instead of iterating the whole table
Repeating the same expression several times Variable (VAR) Evaluated once
/ with possible zero DIVIDE Avoids errors; readable
Calculated column that could be a measure Measure Not stored, computed only when needed
Bidirectional filters everywhere Single direction, bridge via CROSSFILTER in a measure when needed Fewer filter propagations
Many-to-many relationships on large tables Bridge table, star schema Smaller, simpler filter paths

5. Reduce granularity

Granularity (grain) is the level of detail of a table: one row per order line, per order, per day. If reports never need the detail, store a coarser grain.

Ways to reduce it:

  1. Group by in Power Query (or SQL view): summarize the fact to day × product × customer with Sum of Amount, Sum of Quantity, Count of orders. Because some measures do not aggregate (distinct counts, medians), decide what must be kept before summarizing.
  2. Reduce the precision of a column: truncate timestamps to dates or hours, round numbers, group numeric ranges in bands (bins) for slicing.
  3. Split a high-cardinality column into two lower-cardinality columns (date and time; a long code into prefix and suffix).
  4. Aggregations (user-defined): keep a detailed table in DirectQuery and a small summarized table in Import; configure the aggregation table with Manage aggregations so that queries at a high level hit the summary and only drill-downs go to the source. Related approach: composite models with a mix of storage modes (Dual for dimensions).
  5. Incremental refresh to limit what is refreshed; hybrid tables keep the latest partition in DirectQuery.

The trade-off: coarser grain means smaller, faster models but you cannot drill to detail that you removed. Choose the grain based on the lowest level the business questions need.

Comparison of options for a very large fact table

Option Model size Query speed Detail kept Typical use
Import, full detail Large Fast Full Moderate volume
Import, summarized grain Small Fastest Reduced Reports do not need line detail
DirectQuery Tiny Depends on the source Full Real time or huge volume
Composite with aggregations Medium Fast for summary, slower on drill Full (in source) Large volume with summary dashboards

6. Pitfalls and exam tips

  • Hiding is not removing: hidden columns still use memory.
  • Calculated columns increase model size: prefer measures, or create the column upstream in Power Query or the source.
  • Performance Analyzer shows where time goes; DAX query view lets you test a fix. The exam may ask which tool to use first: Performance Analyzer.
  • High display time is not a DAX problem. Reduce the number of marks in the visual.
  • Disable auto date/time and use one shared date table.
  • Cardinality is about distinct values, not rows. Removing a unique ID column often helps more than removing thousands of rows.
  • Exam tip: "reduce model size without losing needed reports" usually means removing unused columns/rows, correcting data types and summarizing; "slow measure" usually means rewriting DAX and testing it in DAX query view.

7. Mini case study

Situation. Northwind Energy has an Import model of 1.2 GB with meter readings: FactReadings (ReadingTimestamp, MeterKey, kWh, DeviceFirmware, RawPayload), one reading per meter per minute (400 million rows). Dashboards show daily consumption per region and month. The page "Overview" takes 18 seconds. Performance Analyzer shows 15.5 s of DAX query and 1.2 s of visual display for the main chart, whose measure is FILTER ( ALL ( FactReadings ), FactReadings[kWh] > 0 )-based.

Question. What do you do?

Solution, commented.

  1. Diagnose. The 15.5 s DAX query time (not display time) means model and measure; the visual is not the problem.
  2. Remove columns DeviceFirmware and RawPayload (unused) and split or drop ReadingTimestamp to date and hour if hours matter.
  3. Reduce granularity. Reports show daily values, so group the fact by date, meter and region to one row per meter per day (a 1,440× reduction in rows). Keep a DirectQuery detail table or a drillthrough source only if analysts occasionally need minute-level data (composite model with an aggregation table).
  4. Rewrite the measure. Replace the full-table FILTER by CALCULATE ( SUM ( FactReadings[kWh] ), FactReadings[kWh] > 0 ) and test both versions in DAX query view for timing.
  5. Re-measure with Performance Analyzer.

Why not the alternatives. Hiding the unused columns changes nothing in memory. Moving everything to DirectQuery pushes 400 million rows of work onto the source. Adding more visuals to "split" the load increases queries.

Key terms

  • VertiPaq: in-memory columnar engine used by Import mode.
  • Cardinality: number of distinct values in a column.
  • Granularity (grain): level of detail of a table row.
  • Performance Analyzer: Desktop and service tool recording DAX query, display and other time per visual.
  • DAX query view: Desktop view to write and run DAX queries and test measures.
  • Aggregation table: summarized table used to answer high-level queries.
  • Composite model: model mixing Import, DirectQuery and Dual tables.
  • Incremental refresh: refreshing only new or changed partitions.
  • Auto date/time: hidden date tables created per date column.

Summary checklist

  • I can explain why cardinality, not row count alone, drives model size.
  • I can remove unnecessary rows and columns and disable auto date/time.
  • I can pick efficient data types and split timestamps.
  • I can read Performance Analyzer's DAX query, visual display and other times.
  • I can run, edit and test a visual's query in DAX query view.
  • I can reduce granularity with grouping, aggregations or composite models.
  • I know hiding a column does not reduce model size.

Official sources