Senior Power BI Developer Interview Questions &amp; Answers

Show answer

Running Total = VAR maxDate = MAX('Date'[Date]) RETURN CALCULATE( [Total Sales], 'Date'[Date] <= maxDate, REMOVEFILTERS('Date') ) REMOVEFILTERS clears the current period filter so all earlier dates are included. In a visual-only scenario, the visual calculation RUNNINGSUM is simpler.

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.

Your workspace has grown to 150 items and nobody knows which reports are used. How do you clean it up?

Power BI Service, Sharing and SecurityMedium

Show answer

I would export usage from the activity log or the workspace usage metrics, list items not viewed in 90 days and confirm with owners before archiving. Duplicate models are consolidated into shared semantic models, naming standards are applied, and workspaces are split by domain with clear owners. A quarterly review keeps it tidy. This also reduces refresh load on capacity.

Managers want to see the top 5 products plus an 'Others' row that sums the rest. How do you build it?

Show answer

I would create a calculated table of product names plus an Others row, related or disconnected, and a measure that ranks products with RANKX over ALL products. For rows ranked 1 to 5 it returns their sales; for Others it returns total sales minus the top 5. TOPN helps compute the top 5 total. The rank should respect slicers like year so the top 5 updates dynamically.

What is the difference between ISFILTERED, ISINSCOPE and HASONEVALUE?

Show answer

ISFILTERED returns true if a column has a direct filter. HASONEVALUE returns true when exactly one value is visible, useful before SELECTEDVALUE. ISINSCOPE returns true when a column is the current grouping level in a visual hierarchy, which is the most reliable way to detect subtotal versus detail rows in matrices. For example, IF(ISINSCOPE('Product'[Product]), [Sales], BLANK()) hides subtotals.

Write a 3-month rolling average measure.

Show answer

Sales 3M Avg = VAR periods = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH) RETURN CALCULATE( AVERAGEX(VALUES('Date'[Month Year]), [Total Sales]), periods ) AVERAGEX over months averages monthly totals instead of daily values. Month Year must be a column in the date table.

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 manager must see their whole reporting hierarchy, not just their own team. How do you build RLS for that?

Show answer

I would flatten the employee hierarchy with PATH(Employee[EmployeeID], Employee[ManagerID]) into a path column, then in the role filter use PATHCONTAINS(Employee[Path], LOOKUPVALUE(Employee[EmployeeID], Employee[Email], USERPRINCIPALNAME())). Each manager then sees every employee below them at any depth. I would test with a few known hierarchies and check performance on a large employee table.

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.

What are calculation groups?

Show answer

Calculation groups are reusable sets of calculation items that modify any measure, such as Current, YTD, Previous Year and YoY %, using SELECTEDMEASURE(). Instead of writing YTD versions of 30 measures, you write one item that applies to all. They can be created in Power BI Desktop model view or Tabular Editor. They reduce measure sprawl but change how measures behave, so naming and format strings need care.

A report must be embedded in a customer-facing web portal for users without Power BI licences. What approach do you take?

Show answer

I would use Power BI Embedded with the 'embed for your customers' pattern, also called app owns data. The application authenticates with a service principal, generates embed tokens and passes customer identity for RLS, so each customer sees only their data. It needs Fabric or embedded capacity. I would design RLS carefully, test token expiry and monitor capacity load as the customer base grows.

Write a measure that counts new customers in a period.

Show answer

New Customers = VAR minDate = MIN('Date'[Date]) RETURN COUNTROWS( FILTER( VALUES(Sales[CustomerID]), CALCULATE(MIN(Sales[OrderDate]), REMOVEFILTERS('Date')) >= minDate ) ) A customer is new if their first ever order falls in the current period.

Tell me about the most complex DAX problem you solved.

Show answer

Situation: finance needed month-end inventory balances, which are semi-additive and must not be summed across months. Task: build balances that work at any date level. Action: I wrote a measure using LASTNONBLANK over dates to take the closing balance per period and SUMX across warehouses, then validated against ERP reports for six months. Result: the report replaced a manual Excel process and matched ERP within rounding.

What does the XMLA endpoint allow you to do?

Show answer

The XMLA endpoint exposes semantic models on Premium, PPU or Fabric capacity like an Analysis Services server. Tools such as Tabular Editor, DAX Studio, SQL Server Management Studio and ALM Toolkit can then read or write the model, manage partitions, refresh specific tables, script deployments and apply object-level security. Read-write mode must be enabled in capacity settings by an admin.

Users export large amounts of sensitive data from reports to Excel. How do you control it?

Show answer

I would review whether export is really needed and adjust export settings per report or at tenant level, for example allowing only summarised data. I would apply Purview sensitivity labels so exported files keep protection, use RLS and OLS to limit what can be exported, and monitor exports through the activity log. For legitimate detailed needs, a governed paginated report is better.

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.

Write a measure to calculate a closing balance that is not summed across months.

Show answer

Closing Balance = CALCULATE( SUM(Inventory[Qty]), LASTNONBLANK('Date'[Date], CALCULATE(COUNTROWS(Inventory))) ) Balances are semi-additive: they sum across warehouses but not across time. This takes the last date with data in each period. LASTDATE works when balances exist for every day.

Thirty measures each need YTD, previous year and YoY versions. How do you avoid 120 measures?

Show answer

I would create a calculation group, such as Time Calculation, with items Current, YTD, PY and YoY % that use SELECTEDMEASURE() wrapped in the time intelligence logic, and set format strings for percent items. Users pick the item from a slicer or matrix columns. This keeps the model small and consistent. I would document it because calculation groups change measure behaviour everywhere.

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.

What is TREATAS and when do you use it?

Show answer

TREATAS applies the values of one table or column as filters on columns of another table, creating a virtual relationship without a physical one. For example, CALCULATE([Budget], TREATAS(VALUES('Product'[Category]), Budget[Category])) filters budget by selected categories even when no relationship exists. It is useful for different-grain tables or disconnected slicers, though physical relationships usually perform better.

Write a dynamic RLS rule using a security mapping table.

Show answer

// Security table: Email, Region; related to Region[Region] // Role filter on Security table: [Email] = USERPRINCIPALNAME() // Optional all-access rule on Region table instead: VAR u = USERPRINCIPALNAME() RETURN LOOKUPVALUE(Security[Region], Security[Email], u) = "ALL" || Region[Region] IN CALCULATETABLE(VALUES(Security[Region]), Security[Email] = u)

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.

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.

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.

What are endorsement, sensitivity labels and lineage view?

Power BI Service, Sharing and SecurityMedium

Show answer

Endorsement marks content as Promoted by its owners or Certified by authorised reviewers, guiding users to trusted semantic models. Sensitivity labels from Microsoft Purview classify content, such as Confidential, and labels flow to exports like Excel and PDF. Lineage view shows how data sources, dataflows, semantic models, reports and dashboards depend on each other, which helps impact analysis before changes.

What is KEEPFILTERS?

Show answer

KEEPFILTERS changes how a CALCULATE filter argument combines with the existing filter context: instead of replacing filters on that column, it intersects with them. For example, CALCULATE([Sales], KEEPFILTERS('Product'[Color] = "Red")) shows blank for Blue rows in a colour table, while without KEEPFILTERS every row would show Red sales. It is useful for measures that should respect user selections.

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.

What are visual calculations?

Show answer

Visual calculations are DAX calculations defined directly on a visual and computed on the visual's own aggregated data, rather than on the model. They make running totals, moving averages, percent of parent and comparison with previous rows simpler using functions like RUNNINGSUM, MOVINGAVERAGE and PREVIOUS. They do not add measures to the model, so they suit visual-specific logic but cannot be reused across visuals.

A YTD measure returns blank for future months on a chart. Users want the line to stop at today. How?

Show answer

The issue is the opposite for many teams: YTD repeats the last value for future months. I would add a condition so the measure returns BLANK when the period starts after the last date with data, for example IF(MIN('Date'[Date]) > [Last Sales Date], BLANK(), TOTALYTD(...)). Alternatively add a column flagging dates up to today in the date table and filter the visual on it.

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 object-level security?

Show answer

Object-level security hides entire tables or columns from specific roles, so users in that role cannot see or query them, for example hiding a Salary column from non-HR users. It is configured with Tabular Editor or similar external tools on the model's roles. Unlike RLS, which filters rows, OLS removes metadata access. Visuals using a secured object show an error for restricted users.

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.

How do you ensure your report numbers are trusted by finance users?

Visualisation and Report DesignMedium

Show answer

I reconcile key measures with the finance system for several periods before launch and document definitions, such as whether revenue is net of returns and taxes, in measure descriptions and a glossary page. I add a refresh timestamp to each page and set up data quality checks. When differences appear, I explain them quickly. Certified semantic models signal that numbers went through review.

Explain how RANKX works and common mistakes with it.

Show answer

RANKX iterates a table, evaluates an expression for each row and ranks the current value among them. A common pattern is RANKX(ALL('Product'[Product]), [Total Sales]). Mistakes include not using ALL, so every product ranks 1 because only one is visible, ranking over a table with duplicates, and forgetting ties handling with the Dense option. The newer RANK function supports multi-column ordering.

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.

Your source data is 2 billion rows in a cloud warehouse and users need near real-time numbers. Which mode do you choose?

Power BI FundamentalsHard

Show answer

Importing 2 billion rows is heavy and refresh-bound, so I would use DirectQuery or, on Fabric with data in OneLake, Direct Lake. To keep reports fast I would add aggregation tables in Import mode for common summary queries, keep detail in DirectQuery, and use a composite model. I would also make sure the warehouse has good indexing or clustering for the filter columns.

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

Show answer

Desktop runs on a local machine with its own memory and only you as user, while the Service shares capacity with other workloads and users, so throttling or memory pressure may appear. Also check RLS, which adds filters that do not exist in Desktop tests, gateway latency for DirectQuery, and differences in data volume after a full refresh. Testing with View as role and capacity metrics helps pinpoint it.

What is the difference between SAMEPERIODLASTYEAR, DATEADD and PARALLELPERIOD?

Show answer

SAMEPERIODLASTYEAR shifts the current dates back exactly one year. DATEADD shifts by any number of days, months, quarters or years, such as DATEADD('Date'[Date], -1, MONTH) for last month. PARALLELPERIOD also shifts by an interval but returns the full shifted period, for example the whole previous quarter even if only one month is selected. Choose based on whether partial periods should be compared.

A measure works in a card but gives wrong results in a matrix with subtotals. How do you debug it?

Show answer

I would test the measure at each level of the matrix using a table visual and check how filter context differs at the subtotal. Often logic uses SELECTEDVALUE or MAX, which return blank or misleading results when several values are visible. I would use ISINSCOPE to branch logic or SUMX over the grouping column for additive results, and validate with DAX Studio queries.

How do you analyse model size with DAX Studio?