Performance Optimisation and Administration Interview Questions for Power BI

Show answer

Remove unused columns and rows, avoid high-cardinality columns like GUIDs and datetime with seconds by splitting date and time or rounding, use integer surrogate keys, prefer measures over calculated columns, turn off Auto date/time, and choose the right data types, such as fixed decimal. Load only the history users need, and summarise data where detail is not required.

Why can too many visuals on a page slow down a report?

Show answer

Each visual sends at least one DAX query when the page loads or when filters change, and visuals render in the browser. A page with 30 visuals, especially complex tables and custom visuals, sends many queries in parallel, competing for capacity and causing long load times. Reducing visuals, combining cards into one multi-row card or using tooltips and drill-through pages improves speed.

How do you use Performance Analyzer in Power BI Desktop?

Show answer

Open View, Performance Analyzer, click Start recording and then Refresh visuals or interact with the page. Each visual shows total duration split into DAX query, visual display and other. Sort by duration to find the slowest visuals, expand them and choose Copy query to paste into DAX Studio for deeper analysis. Clear the cache between tests so timings are realistic.

How do you stay current with frequent Power BI updates?

Show answer

Power BI releases features monthly, so I read the monthly update blog and Fabric release notes, try new features on a test report, and follow community experts and forums. I keep a short list of features relevant to our team, such as visual calculations or TMDL view, and share a five-minute demo in team meetings when something will save us effort.

How does the VertiPaq engine store and compress data?

Show answer

VertiPaq is the in-memory columnar engine for Import models. It stores each column separately and compresses it using dictionary encoding, which maps unique values to integers, value encoding for numbers, and run-length encoding for repeated sequences. Compression depends mostly on column cardinality, so columns with few unique values compress very well, while unique IDs and precise timestamps take most of the memory.

What tools do you use to analyse Power BI performance?

Show answer

Performance Analyzer in Desktop records how long each visual takes for DAX query, visual display and other work, and lets you copy the query. DAX Studio runs those queries with server timings and query plans and includes VertiPaq Analyzer for model size by table and column. Best Practice Analyzer in Tabular Editor flags modelling issues. In the Service, capacity metrics apps show load on Premium or Fabric capacity.

What does the Best Practice Analyzer do?

Show answer

Best Practice Analyzer, in Tabular Editor and also available in Fabric notebooks through semantic link labs, scans a semantic model against a set of rules, such as avoiding bi-directional relationships, hiding foreign keys, not summarising numeric IDs, using DIVIDE, and setting format strings. It lists violations with their severity so teams can fix them before release. Rules can be customised to match team standards.

A report page takes 25 seconds to load. Walk me through how you would fix it.

Show answer

I would run Performance Analyzer to find the slowest visuals, copy their DAX queries into DAX Studio and check server timings. Common fixes are rewriting measures that iterate large fact tables, removing bi-directional relationships, reducing visuals on the page, and shrinking the model by removing high-cardinality columns. If the source is DirectQuery, I would add aggregations or move to Import. Then re-measure after each change.

How do you analyse model size with DAX Studio?

Show answer

Connect DAX Studio to the open Power BI Desktop file, go to Advanced, View Metrics to run VertiPaq Analyzer. It shows size per table and column, cardinality, encoding and relationship sizes. Sort columns by total size to find the biggest consumers, often IDs, timestamps or long text. You can export a vpax file to compare before and after optimisation or share with colleagues.

Rewrite a slow measure to use variables and avoid repeated calculation.

Show answer

Before: Growth = IF([Sales PY] = 0, BLANK(), ([Total Sales] - [Sales PY]) / [Sales PY]) After: Growth = VAR cur = [Total Sales] VAR py = [Sales PY] RETURN DIVIDE(cur - py, py) The previous year measure is now evaluated once instead of three times, and DIVIDE handles zero safely.

How do you run a DAX query in DAX Studio to test a measure?

Show answer

EVALUATE SUMMARIZECOLUMNS( 'Date'[Fiscal Year], 'Product'[Category], "Sales", [Total Sales], "Margin", [Margin %] ) ORDER BY 'Date'[Fiscal Year] Turn on Server Timings and Clear cache before running to see storage engine and formula engine time. The same EVALUATE syntax works in the DAX query view in Desktop.

Split a datetime column to reduce cardinality in Power Query.

Show answer

= Table.AddColumn(Source, "OrderDateOnly", each DateTime.Date([OrderDateTime]), type date) = Table.AddColumn(Prev, "OrderHour", each Time.Hour([OrderDateTime]), Int64.Type) Then remove the original OrderDateTime column. A datetime with seconds can have millions of unique values, while date and hour columns have only thousands and 24 values, compressing much better.

How do you explain a technical limitation to a business user who wants something impossible?

Show answer

I acknowledge the goal, explain the limitation in simple terms and offer an alternative that meets most of the need. For example, when a user wanted live updates every second on a DirectQuery report over a slow system, I explained the load it would create and offered a 15-minute refresh plus an alert for threshold breaches. They accepted because the key decision was covered.

Users need to filter a report by 5,000 product names using a slicer, and it is slow. What do you do?

Show answer

A slicer with thousands of values sends heavy queries and is hard to use. I would add a hierarchy so users pick category and subcategory first, enable the slicer search box, and consider a text filter visual for direct search. Apply buttons in query reduction settings stop queries on every click. If the slicer depends on a measure-filtered list, I would simplify that logic.

Describe how you would review another developer's Power BI model before release.

Show answer

I check the model diagram for a clean star schema and single-direction relationships, run Best Practice Analyzer, and review VertiPaq Analyzer for oversized columns. I read key measures for correctness and naming, check that keys are hidden and formats set, test RLS roles, and run Performance Analyzer on main pages. I share findings as a short prioritised list, explaining why each change matters.

What are the formula engine and storage engine in Power BI?

Show answer

The storage engine, VertiPaq or DirectQuery to the source, retrieves and aggregates data quickly and in parallel. The formula engine handles complex DAX logic that the storage engine cannot, working single-threaded on intermediate results. Fast measures push most work to the storage engine. DAX Studio's server timings show time spent in each, helping identify measures that create large intermediate data caches for the formula engine.

What are aggregation tables and how do they improve performance?

Show answer

Aggregation tables store pre-summarised data, such as sales by day, product and store, in Import mode, while detail stays in a large DirectQuery table. With Manage aggregations, Power BI automatically answers queries from the small aggregation when the requested grain allows it and falls back to detail otherwise. This gives fast summary visuals on billion-row data while still allowing drill to detail.

What is the Dual storage mode?

Show answer

Dual mode tables act as Import or DirectQuery depending on the query. In a composite model, dimension tables set to Dual can be served from memory when joined to Import aggregation tables, and sent to the source when joined to DirectQuery fact tables, avoiding slow cross-source joins. It is commonly used for dimensions in models that mix aggregations with DirectQuery detail.

What is Microsoft Fabric capacity and how do capacity units work?

Show answer

Fabric capacity is a pool of compute purchased as F SKUs, from F2 upward, measured in capacity units that all workloads share, including Power BI semantic models, refreshes, notebooks and pipelines. Usage is smoothed over time, so short spikes are allowed, but sustained overuse causes throttling where interactive queries are delayed or rejected. Admins use the Fabric Capacity Metrics app to monitor and plan sizing.

The model is 9 GB and refresh times out. What would you do?

Show answer

I would analyse it with VertiPaq Analyzer to find the largest columns and tables, remove unused columns, split or round datetime columns, replace text keys with integer keys and drop history users do not need. Then set up incremental refresh on large fact tables so only recent partitions reload. If needed, push heavy transformations upstream into the warehouse and consider Direct Lake on Fabric.

A DirectQuery report is slow and the database team complains about load. What do you do?

Show answer

I would check which queries are slow using Performance Analyzer and the database's query logs. Then reduce visuals per page, add Import-mode aggregation tables for common summary views, set dimensions to Dual mode, and apply query reduction options so slicers send queries only on Apply. On the source side, indexes or columnstore tables help. If real-time is not essential, moving to Import with frequent refresh may be simpler.

Reports slow down every morning at 9 a.m. on shared capacity. What could be happening?

Show answer

Morning peaks often combine many users opening reports with heavy scheduled refreshes running at the same time. I would check the Fabric Capacity Metrics app for throttling and identify the heaviest items. Fixes include moving refreshes to off-peak hours, optimising the largest semantic models, spreading workloads across capacities, or scaling the capacity. Communicating refresh windows to teams also helps.

Your measure using IF and many nested CALCULATE calls is slow. How do you optimise it?

Show answer

I would store repeated sub-expressions in variables so they are computed once, replace nested IF chains with SWITCH(TRUE(), ...), use column filters instead of FILTER over tables, and avoid context transition inside large iterators. I would also check whether some logic can move into a calculated column or Power Query at refresh time. DAX Studio timings confirm whether formula engine time drops.

Tell me about a time you improved the performance of a slow report.

Show answer

Situation: a sales report on DirectQuery took over 30 seconds per page and users had stopped opening it. Task: get pages under 5 seconds. Action: I added an Import aggregation table at day and store level, set dimensions to Dual, reduced the main page from 18 to 9 visuals and rewrote two FILTER-heavy measures. Result: page load fell to about 3 seconds and weekly active users doubled.

A large model's measures are fast in Desktop but slow in the Service. What could explain it?