In this post we are going to cover performance optimization in Power BI: finding a slow visual, how VertiPaq compresses data, shrinking a model, writing faster DAX, aggregations and composite models, and report design. These are 7 real Power BI interview questions, and the last one is a scenario a manager loves to ask: the CEO says the sales dashboard takes 15 seconds to load.
Performance questions separate people who have supported reports in production from people who have only built them. Anyone can say "the report is slow". Interviewers want to hear how you find the cause, which tool you open first, and how you prove the fix worked.
Every example below runs on a small sample data set, so you can rebuild it in Power BI Desktop and check each number yourself.
Part 7 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: How do you find out why a report page is slow?
- Question 2: How does the VertiPaq engine compress data, and what makes an import model big?
- Question 3: How do you reduce the size of a semantic model?
- Question 4: How do you write faster DAX?
- Question 5: What are aggregations and composite models, and when do you use them?
- Question 6: How does report design affect performance, and what is the Best Practice Analyzer?
- Question 7: Scenario - the CEO says the sales dashboard takes 15 seconds to load. Walk me through what you do.
- The full SQL script
The short answer
Measure first: Performance Analyzer and server timings show where the time goes. Model size is driven by cardinality, DAX should filter columns, not tables, and huge DirectQuery facts get aggregation tables with Dual dimensions.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales. Twelve rows will never be slow, of course, but they are enough to show how compression and DAX patterns work.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
And the customers, with the region column we use for the compression example.
| customer_key | customer | city | region | |
|---|---|---|---|---|
| 1 | Sara Khan | Toronto | East | sara@tbshop.com |
| 2 | Omar Ali | Dallas | West | omar@tbshop.com |
| 3 | Lina Park | Seattle | West | lina@tbshop.com |
| 4 | Mei Chen | Boston | East | mei@tbshop.com |
Question 1: How do you find out why a report page is slow?
Measure first, don't guess. Start with Performance Analyzer, on the Optimize ribbon in Power BI Desktop. Start recording, refresh the visuals, and every visual gets its own timing.
It splits the time of each visual into categories:
| Category | What it means |
|---|---|
| DAX query | the engine answers the query |
| Direct query | waiting on the DirectQuery source |
| Visual display | drawing the visual |
| Other | preparing, waiting on other visuals |
Then copy the query of the slowest visual and run it in DAX query view, or in DAX Studio with server timings switched on.
Server timings split the time between two engines:
- The storage engine scans the compressed data, and it is multi-threaded.
- The formula engine runs the DAX logic, and it is single-threaded.
If most of the time is in the formula engine, rewrite the DAX. If it is in the storage engine, look at the model and the data size.
In the interview, name the tools in order: Performance Analyzer to find the visual, then DAX Studio with server timings to find the engine.
Question 2: How does the VertiPaq engine compress data, and what makes an import model big?
VertiPaq is a columnar engine: every column is stored on its own. For text columns it uses dictionary encoding. Each distinct value goes into a dictionary once, and each row just stores a small number that points to its dictionary entry.
Let's see that on the region of each sale.
| value | dict_id | sales_rows |
|---|---|---|
| East | 1 | 6 |
| West | 2 | 6 |
Check this result yourself with SQL
Twelve rows, but only two distinct values: the dictionary has two entries and each row stores a small ID.
SELECT c.region AS value,
DENSE_RANK() OVER (ORDER BY c.region) AS dict_id,
COUNT(*) AS sales_rows
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
ORDER BY c.region;There are 12 sales rows, but only two dictionary entries: East with id 1 and West with id 2, each used by 6 rows. Twelve rows store a tiny number, and the text is stored only twice. That compresses extremely well.
So model size is driven by cardinality, the number of distinct values in a column, more than by row count. Here are the distinct values per column of our sales.
| column_name | distinct_values |
|---|---|
| sale_id | 12 |
| order_date | 12 |
| unit_price | 5 |
| product_key | 5 |
| customer_key | 4 |
| region | 2 |
Check this result yourself with SQL
sale_id has one distinct value per row, so it barely compresses; region has two.
SELECT column_name, distinct_values FROM (
SELECT 'sale_id' AS column_name, COUNT(DISTINCT f.sale_id) AS distinct_values, 1 AS srt FROM fact_sales f
UNION ALL SELECT 'order_date', COUNT(DISTINCT f.order_date), 2 FROM fact_sales f
UNION ALL SELECT 'unit_price', COUNT(DISTINCT f.unit_price), 3 FROM fact_sales f
UNION ALL SELECT 'product_key', COUNT(DISTINCT f.product_key), 4 FROM fact_sales f
UNION ALL SELECT 'customer_key', COUNT(DISTINCT f.customer_key), 5 FROM fact_sales f
UNION ALL SELECT 'region', COUNT(DISTINCT c.region), 6
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key) t
ORDER BY srt;sale_id has 12 distinct values in 12 rows, one per row, so it barely compresses. Region has only 2. On a real model, the big columns are the same kind: IDs, datetime columns with seconds, and free text.
Other things that make a model big:
- Calculated columns - stored in memory.
- Unused columns - every column costs memory.
- Auto date/time - hidden date tables.
To see exactly where the memory goes, run VertiPaq Analyzer in DAX Studio and sort the columns by size.
Question 3: How do you reduce the size of a semantic model?
Remove what nobody uses, then reduce cardinality. Here is the checklist a senior developer would give.
| Fix | Why it helps |
|---|---|
| Remove unused columns and rows | every column costs memory, even hidden |
| Split datetime into date and time | far fewer distinct values |
| Reduce precision | round decimals, drop seconds |
| Measures, not calculated columns | computed at query time, not stored |
| Right data types | numeric keys compress better than text |
| Disable Auto date/time | no hidden date table per date column |
| Summarize old history | keep detail only where it is needed |
A few of these deserve a sentence each in the interview:
- Unused columns cost memory even when they are hidden, so drop them, and filter out rows you don't need, like very old history.
- Split a datetime into a date and a time. A date column has at most 366 values a year, so it compresses far better than a timestamp with seconds.
- Numeric keys compress better than text keys.
Create derived columns in Power Query or at the source, not as DAX calculated columns, because they compress better.
Question 4: How do you write faster DAX?
Filter columns, not tables, use variables, and use DIVIDE instead of IFERROR.
Here is a typical slow measure. It calculates the share of sales that come from multi-unit sales lines (quantity 2 or more). It uses FILTER over the whole Sales table inside CALCULATE, and wraps everything in IFERROR.
Multi Unit Share =
IFERROR (
CALCULATE (
[Total Sales],
FILTER ( Sales, Sales[Qty] >= 2 )
) / [Total Sales],
BLANK ()
)Here is the faster version. KEEPFILTERS with a column predicate keeps the same meaning, and the engine can apply it as a simple column filter. A variable holds the result, and DIVIDE handles division by zero, so we don't need IFERROR.
Multi Unit Share =
VAR MultiUnit =
CALCULATE (
[Total Sales],
KEEPFILTERS ( Sales[Qty] >= 2 )
)
RETURN
DIVIDE ( MultiUnit, [Total Sales] )Both versions return the same numbers.
| region | multi_unit_sales | total_sales | share_pct |
|---|---|---|---|
| East | 720.00 | 1030.00 | 69.90 |
| West | 390.00 | 890.00 | 43.82 |
| Total | 1110.00 | 1920.00 | 57.81 |
Check this result yourself with SQL
The slow and the fast measure return the same numbers: 720 East, 390 West, 1110 total (57.81 percent).
SELECT region, multi_unit_sales, total_sales,
CAST(100.0 * multi_unit_sales / total_sales AS DECIMAL(5,2)) AS share_pct
FROM (
SELECT c.region, 0 AS srt,
SUM(CASE WHEN f.qty >= 2 THEN f.qty * f.unit_price END) AS multi_unit_sales,
SUM(f.qty * f.unit_price) AS total_sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
UNION ALL
SELECT 'Total', 1,
SUM(CASE WHEN f.qty >= 2 THEN f.qty * f.unit_price END), SUM(f.qty * f.unit_price)
FROM fact_sales f) t
ORDER BY srt, region;Multi-unit sales are 720.00 in East and 390.00 in West, 1110.00 in total. That is 57.81 percent of all sales of 1920.00. The point is not the numbers, it is that the rewrite changes speed, not meaning.
The rules to remember:
- Variables - evaluated once.
- Filter columns - not FILTER over a whole table.
- DIVIDE - instead of IFERROR, and avoid IFERROR inside iterators.
- No nested iterators over big tables.
And always measure the change in DAX Studio. Don't guess.
Question 5: What are aggregations and composite models, and when do you use them?
When the fact table is too big to import, you keep the detail in DirectQuery and add an aggregation table: a small Import table, summarized, for example by month and product.
Here is how a query flows:
- A report visual sends a query.
- If the aggregation table can answer it, the query is served from memory.
- If the user drills down to detail, the query goes to the DirectQuery source.
Set the shared dimensions, like Date and Product, to Dual storage mode. A Dual table acts as Import or DirectQuery depending on the query, so it works with both sides, and you avoid limited relationships.
Use this for very large facts where most visuals show summaries. A model that mixes Import and DirectQuery tables like this is a composite model.
Row-level security must filter both the aggregation table and the detail table. Automatic aggregations also exist, but they need Premium, PPU or Fabric capacity.
Question 6: How does report design affect performance, and what is the Best Practice Analyzer?
Every visual sends its own query. A page with thirty visuals sends at least thirty queries, so a fast model can still give a slow page.
What to do about it:
- Fewer visuals - keep each page focused, and combine cards into one visual where you can.
- Query reduction - slicers and cross-highlighting fire new queries on every click. The Query reduction preset on the Optimize ribbon turns off cross-highlighting and adds Apply buttons. It is the quick win for DirectQuery reports.
- No huge table visuals on the landing page - avoid big tables with high-cardinality columns there.
- Drillthrough and bookmarks - show summaries first, detail on demand.
Then run a Best Practice Analyzer. Tabular Editor has one, and in Fabric you can open a Best Practice Analyzer notebook from the semantic model. It flags things like:
- Floating point data types
- Bidirectional relationships
- IFERROR in measures
- Auto date/time turned on
Question 7: Scenario - the CEO says the sales dashboard takes 15 seconds to load. Walk me through what you do.
This is the scenario managers love. They don't want a list of tips. They want a method.
Reproduce it first. Same page, same filters, and the same security role as the CEO. A report can be fast for you and slow for the CEO because of filters or row-level security. Then run Performance Analyzer and find the worst visual.
Here is an example of what you might see. These timings are made up for illustration, they don't come from the sample data.
| Visual | DAX query | Display |
|---|---|---|
| Sales matrix | 9.0 s | 0.3 s |
| Map | 0.4 s | 0.6 s |
| KPI cards | 0.2 s | 0.1 s |
In this example, the sales matrix takes 9.0 seconds, and almost all of it is DAX query, only 0.3 seconds of display. So it is the query, not the rendering. Copy it into DAX Studio and check server timings.
Then walk through the rest in order:
- Check the storage mode. If it is DirectQuery, the time may be at the source, so look at the source query, indexes and aggregations.
- Check the model size. If it is Import, run VertiPaq Analyzer.
- Fix the biggest problem first, then measure again with the same test, and write down the before and after.
- Set expectations. Tell the CEO what you found, what you changed, and what load time is realistic.
Measure, fix, measure again. Report the before and after, not a guess. Managers want a method and a number.
Key points to remember
- Measure first: Performance Analyzer and server timings
- Model size is driven by cardinality
- Cut columns, split datetime, disable Auto date/time
- DAX: variables, column filters, DIVIDE
- Aggregations and Dual for huge DirectQuery facts
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind every result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #7 - Performance optimization
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q07; SET search_path TO pbi_q07;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Dictionary encoding: Region in the Sales rows
SELECT c.region AS value,
DENSE_RANK() OVER (ORDER BY c.region) AS dict_id,
COUNT(*) AS sales_rows
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
ORDER BY c.region;
-- Step 2: Cardinality: distinct values per column of Sales
SELECT column_name, distinct_values FROM (
SELECT 'sale_id' AS column_name, COUNT(DISTINCT f.sale_id) AS distinct_values, 1 AS srt FROM fact_sales f
UNION ALL SELECT 'order_date', COUNT(DISTINCT f.order_date), 2 FROM fact_sales f
UNION ALL SELECT 'unit_price', COUNT(DISTINCT f.unit_price), 3 FROM fact_sales f
UNION ALL SELECT 'product_key', COUNT(DISTINCT f.product_key), 4 FROM fact_sales f
UNION ALL SELECT 'customer_key', COUNT(DISTINCT f.customer_key), 5 FROM fact_sales f
UNION ALL SELECT 'region', COUNT(DISTINCT c.region), 6
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key) t
ORDER BY srt;
-- Step 3: Multi-unit sales (qty >= 2) and share of sales, by region
SELECT region, multi_unit_sales, total_sales,
CAST(100.0 * multi_unit_sales / total_sales AS DECIMAL(5,2)) AS share_pct
FROM (
SELECT c.region, 0 AS srt,
SUM(CASE WHEN f.qty >= 2 THEN f.qty * f.unit_price END) AS multi_unit_sales,
SUM(f.qty * f.unit_price) AS total_sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
UNION ALL
SELECT 'Total', 1,
SUM(CASE WHEN f.qty >= 2 THEN f.qty * f.unit_price END), SUM(f.qty * f.unit_price)
FROM fact_sales f) t
ORDER BY srt, region;
Part 8: Row-level Security and Governance
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.