Hard Power BI Interview Questions

Direct Lake is a storage mode for semantic models on Fabric capacity that reads Delta Parquet files from OneLake straight into the VertiPaq engine on demand. It avoids scheduled import refreshes and the latency of DirectQuery, so reports show fresh data with near-import performance. If a query exceeds capacity guardrails or uses unsupported features, it can fall back to DirectQuery, which is slower.

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.

Power BI Project (PBIP) saves a report and model as folders of text files instead of one binary PBIX file. The semantic model is stored in TMDL or TMSL and the report in JSON definitions. This makes Git version control, code reviews, diffs and merge of changes practical, and works with Fabric Git integration and deployment pipelines. It is preferred for team development and CI/CD.

Privacy levels, Private, Organizational and Public, control whether data from one source can be sent to another source during combining, to avoid leaking sensitive data. When a query mixes sources with incompatible levels, Power Query may block it with a Formula.Firewall error or stop folding. The fix is to set correct privacy levels for each source or restructure queries so sources are combined in a staged way.

I would fix the immediate step, then reduce dependence on exact column names: avoid hard-coded Changed Type steps on all columns, select only needed columns with MissingField.Ignore where suitable, and standardise names in one place. I would agree a data contract or SQL view with the source team so renames go through a stable interface, and set up refresh failure notifications.

Budgets are often at month and category level while sales are daily and per product. Instead of a direct many-to-many link, relate both fact tables to shared dimensions at the right level: Date and a Category dimension. Budget relates to Category and to Date through a month-level key or the first day of month. Measures then compare them, and visuals should use only the shared grain.

Slowly changing dimensions track attribute changes over time. Type 1 overwrites the old value, so history shows the current attribute. Type 2 adds a new row with a surrogate key and valid-from and valid-to dates, preserving history, such as a customer moving region. In Power BI, Type 2 needs facts to carry the surrogate key, and the warehouse should handle it rather than Power Query.

Likely a relationship is joining at the wrong grain or creating a many-to-many path, so rows get duplicated in filtering, or a bi-directional relationship is letting one fact filter another unexpectedly. I would check the relationships, keys and cardinality, make filters flow from shared dimensions only, and verify a measure against a known total in the source system using a simple table visual.

For example, accounts can have multiple customers and customers multiple accounts. Create a bridge table with unique Account and Customer pairs. Relate Customer to Bridge one-to-many, and Account to Bridge one-to-many, with the Bridge to Account relationship set to filter in both directions or use CROSSFILTER in measures. Facts relate to Account. Test totals carefully because shared accounts count once per customer.

Surrogate keys are system-generated integer keys, such as ProductKey, used in dimensions instead of business keys like product codes. They handle changes in business keys, support Type 2 history with multiple rows per business key, and integer keys compress and join better in VertiPaq than long text keys. They are usually created in the data warehouse layer.

Context transition happens when CALCULATE, or a measure reference which implicitly wraps CALCULATE, is evaluated inside a row context. The values of the current row are converted into filters on the model. For example, in a calculated column on Customer, [Total Sales] returns that customer's sales because the row becomes a filter. It is powerful but can be slow over large tables and surprising with duplicate rows.

A boolean filter like 'Product'[Color] = "Red" is shorthand for a filter over that column's values and replaces existing filters on that column. FILTER(Table, condition) iterates a table and returns rows matching the condition; it can use measures and multiple columns but is slower on large tables. Prefer column filters where possible and use FILTER on small tables or with KEEPFILTERS when needed.

I would replace FILTER over the whole fact table with column filters in CALCULATE, such as CALCULATE([Sales], Sales[Channel] = "Online"), which the engine can apply efficiently. If a condition uses a measure, iterate over a small dimension table instead, like FILTER(VALUES(Customer[CustomerID]), [Sales] > 10000). Then test in DAX Studio with server timings before and after.

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.

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.

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.

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.

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.

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.

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.

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.

Large detail tables are rarely read on screen. I would understand the real need: if users want to search specific records, add slicers or a search filter and a drill-through page; if they need to export, provide a paginated report or analyse in Excel. On the main page I would show aggregated results with top N filters, which improves both speed and readability.

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.

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.

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.

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.

In Power Query create RangeStart and RangeEnd parameters of type Date/Time, and filter OrderDate so it is greater than or equal to RangeStart and less than RangeEnd. Confirm the step folds to the source. In Desktop right-click the table, choose Incremental refresh, set to archive, say, 5 years and refresh the last 10 days, optionally detect data changes. Publish, and the Service creates partitions on first refresh.

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.

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.

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.

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.

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.

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.

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.

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.

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.

I would identify the common tables and measures, then build one certified shared semantic model with a proper star schema and agreed measure definitions. Reports would be rebuilt or rebound as thin reports using live connections to it, starting with the most used ones from activity data. Old models are retired gradually. This cuts refresh load, removes conflicting numbers and makes changes in one place.

Save the report as a Power BI Project (PBIP), commit the folder to a Git repository in Azure DevOps or GitHub, and connect a Fabric workspace to a branch through Workspace settings, Git integration. Changes can then be committed from the workspace or synced from the repository. Developers work on feature branches and merge through pull requests, giving history, reviews and rollback for reports and models.

I would compare row counts and totals at a high level first, then drill down by date and key dimensions to find where the difference starts. Merge both tables with a full outer join on the business key and use anti joins to list missing rows, then compare values on matched rows. Common causes are different cut-off times, filters, duplicates, time zones or currency conversions.

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.

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.