Power BI Preparation for Any Interview or Job Change

Power BI Developer Interview Guide

Questions & Answers Across Data Sources, Modeling, DAX, and Deployment

1. Power BI Fundamentals

Q1. What are the core components of Power BI? A: Power BI Desktop (authoring), Power BI Service (cloud publishing/sharing), Power BI Mobile, Power BI Report Server (on-prem), Power BI Gateway (on-prem data connectivity), and Power BI Premium/Fabric capacity (dedicated compute).

Q2. What is the difference between Power BI Desktop, Power BI Pro, and Power BI Premium? A: Desktop is the free authoring tool. Pro is a per-user license needed to share/collaborate in the Service (workspaces, apps). Premium (capacity-based, now largely under Microsoft Fabric) gives dedicated compute, larger dataset sizes, paginated reports, AI features, and lets viewers without Pro licenses consume content.

Q3. What is a Power BI Dataflow vs a Dataset (Semantic Model)? A: A Dataflow is reusable ETL logic (Power Query Online) that stores transformed entities in Azure Data Lake — shareable across multiple reports/datasets. A Dataset (now called a Semantic Model) is the data model (tables, relationships, measures) that reports are built on.

Q4. What is Import mode vs DirectQuery vs Live Connection vs Composite mode? A:

• Import: Data is copied into Power BI’s in-memory engine (VertiPaq). Fastest performance, but data is only as fresh as the last refresh.
• DirectQuery: No data copied; queries pushed to the source in real time. Always fresh, but performance depends on the source and query complexity.
• Live Connection: Connects directly to an existing SSAS/Power BI dataset model without importing or querying — used for Analysis Services or shared datasets.
• Composite model: Mixes Import and DirectQuery tables in one model, with configurable storage mode per table (Import/DirectQuery/Dual).

Q5. What is the VertiPaq engine? A: The in-memory, columnar storage and query engine used by Power BI/SSAS Tabular for Import mode. It compresses data column-wise (dictionary + RLE encoding) for fast aggregation and scan performance.

2. Connecting to Data Sources

Q6. What are the major categories of data sources Power BI supports? A: Files (Excel, CSV, PDF, XML, JSON, Text), Databases (SQL Server, Oracle, MySQL, PostgreSQL, Teradata, Snowflake, Databricks), Azure services (Azure SQL DB, Synapse, Data Lake, Cosmos DB), Online services (SharePoint, Dynamics 365, Salesforce, Google Analytics, Google BigQuery), and Other (ODBC, OLE DB, Web/REST API, OData, Blank Query/M scripting).

Q7. How do you connect Power BI to a SQL Server database, and what’s the difference between Import and DirectQuery for it? A: Get Data → SQL Server → provide server/database name → choose Import or DirectQuery. Import loads a snapshot into memory (fast, needs scheduled refresh). DirectQuery sends native SQL at query time (real-time but relies on SQL Server performance/indexing, and disables some modeling features like certain DAX time-intelligence functions unless enabled explicitly).

Q8. How do you connect to an Excel file, and what are common pitfalls? A: Get Data → Excel Workbook → select the file → choose specific sheets/tables/named ranges in the Navigator. Pitfalls: unstructured “reports” with merged cells and multiple headers, inconsistent column types, blank rows causing type-detection to misfire, and needing “Promote Headers” or removing top rows before transformation.

Q9. How do you connect Power BI to SharePoint (list or folder)? A: For a SharePoint Online List: Get Data → SharePoint Online List → enter the site URL → authenticate (OAuth) → select the list. For a folder of files: Get Data → SharePoint Folder → enter the site URL → Power BI returns metadata for all files, which you then combine/transform using the “Combine Files” pattern in Power Query.

Q10. How do you pull data from a REST API / Web source? A: Get Data → Web → enter the endpoint URL. For paginated or authenticated APIs, use “Web (advanced)” to add headers, or write custom M code using Web.Contents() with query parameters, headers (e.g., API keys, bearer tokens), and RelativePath. For paginated APIs, build a custom function and use List.Generate() to loop through pages.

Q11. How do you connect to an OData feed? A: Get Data → OData Feed → enter the service root URL (e.g., a Dynamics 365 or SharePoint OData endpoint). Power BI parses the $metadata to expose entities in the Navigator; you can pass OData query options ($filter, $select) to reduce data pulled.

Q12. How do you connect to Azure Data Lake Storage Gen2 or Azure Blob Storage? A: Get Data → Azure → Azure Data Lake Storage Gen2 (or Azure Blob Storage) → provide the account URL → authenticate with account key, SAS token, or Azure AD → browse folder/file listing → use Combine Files or custom M to parse (CSV/Parquet/JSON).

Q13. How do you connect to Dynamics 365 / Salesforce / Google Analytics? A: These are built-in connectors under Get Data → Online Services. You authenticate via OAuth (Microsoft account, Salesforce login, or Google account) and Power BI exposes entities/objects (e.g., Accounts, Opportunities) as tables through their respective APIs — no manual REST calls needed.

Q14. What is an ODBC/OLE DB connection used for? A: Generic connectors used when there’s no native Power BI connector for a data source (e.g., a legacy or niche database) but the vendor provides an ODBC/OLE DB driver. You configure a DSN (or driver connection string) at the OS level, then Get Data → ODBC/OLE DB and select the DSN or paste a connection string.

Q15. How do you connect Power BI to Snowflake or Databricks? A: Get Data → Snowflake: provide server name and warehouse, choose Import or DirectQuery, authenticate with Snowflake credentials or Azure AD/OAuth. For Databricks: Get Data → Azure Databricks, provide the server hostname and HTTP path from the cluster/SQL warehouse, authenticate via personal access token or Azure AD.

Q16. What is the difference between a native SQL query (“Advanced options” in the connector) and using the Navigator/table view? A: The Navigator/table view lets Power BI generate the query automatically and enables full Query Folding. A native/custom SQL query gives you full control (joins, filtering server-side) but Power BI treats it as an opaque step — later Power Query transformations may or may not fold back into it, and it can bypass the connector’s automatic optimization.

Q17. What is a Gateway, and when do you need one? A: The On-premises Data Gateway is a bridge that lets the Power BI Service securely access on-premises or private-network data sources (SQL Server, file shares, SAP, on-prem SharePoint) for scheduled refresh or DirectQuery. There’s also a “Personal Gateway” (single-user, no sharing) and VNet Gateway for private Azure networking without an on-prem gateway.

Q18. How do you combine data from multiple sources of different types (e.g., SQL Server + Excel + a REST API) into one model? A: Power BI is source-agnostic once data lands in the Power Query layer — you load each source as its own query, shape/clean each independently, then merge or append queries, and build relationships between resulting tables in the model. Storage mode (all Import, or Composite) determines whether cross-source DirectQuery is even possible.

Q19. What is Query Folding, and why does it matter across data sources? A: Query folding pushes Power Query transformation steps back to the source system (e.g., generating SQL WHERE/GROUP BY) instead of pulling all raw data and processing it in Power BI’s engine. It matters because it drastically improves refresh performance and reduces data transferred — but it only works with foldable sources (relational databases, OData) and breaks once you use certain M functions (e.g., custom column with complex logic, some text/date functions, or merging with a non-foldable source).

Q20. How do you check if query folding is happening? A: Right-click a step in the Query Editor → if “View Native Query” is enabled (not greyed out), folding is occurring up to that step; clicking it shows the generated SQL.

3. Power Query / Data Transformation (M Language)

Q21. What language does Power Query use under the hood? A: “M” (Power Query Formula Language) — a functional, case-sensitive language. The Advanced Editor exposes the full M script behind any set of UI transformation steps.

Q22. What’s the difference between Table.SelectRows and applying a filter via the UI? A: They’re the same thing — the UI filter dropdown generates a Table.SelectRows(Source, each [Column] = “Value”) step automatically; you can also write it manually in the Advanced Editor for more complex logic.

Q23. How do you parameterize a data source (e.g., switch between Dev/Test/Prod servers)? A: Create Power Query Parameters (Manage Parameters) for server name/database name, reference them in the data source connection step, and use “Data source settings” or deployment pipelines / parameter rules in the Service to swap values per environment.

Q24. What is the “Combine Files” feature used for? A: When connecting to a folder (local, SharePoint, ADLS) containing many files of the same structure (e.g., monthly CSV exports), Combine Files auto-generates a sample query/function that is applied to every file and appends the results into a single table.

Q25. How do you handle a REST API that paginates results? A: Write a custom function that takes a page number/offset/token as a parameter, calls Web.Contents with that parameter, then use List.Generate to iterate calls until an end condition (e.g., empty result or no “next” token) is met, and combine all pages with Table.Combine or List.Union.

Q26. What is the difference between Merge Queries and Append Queries? A: Merge = a join (like SQL JOIN) — combines columns from two tables based on matching key(s). Append = a union — stacks rows from two or more tables with matching (or compatible) columns.

Q27. What are the join kinds available in Merge Queries? A: Left Outer, Right Outer, Full Outer, Inner, Left Anti, Right Anti — same semantics as SQL joins/anti-joins.

Q28. How do you improve Power Query refresh performance? A: Maximize query folding, filter early/reduce columns early (before non-foldable steps), avoid unnecessary “Change Type” churn, disable “Enable Load” for staging queries, use dataflows or aggregation tables for heavy source-side reuse, and avoid Table.Buffer/volatile functions unless required.

4. Data Modeling

Q29. What is a Star Schema, and why is it preferred in Power BI? A: A model with a central Fact table (measures/events, e.g., Sales) surrounded by Dimension tables (descriptive attributes, e.g., Date, Product, Customer) connected by single-direction, one-to-many relationships. It’s preferred because VertiPaq and DAX are optimized for it — it minimizes ambiguous filter paths and improves both performance and comprehensibility versus a Snowflake schema or flat single-table design.

Q30. What’s the difference between a one-to-many, many-to-many, and one-to-one relationship? A: One-to-many: the common case (Dimension “1” side to Fact “many” side). Many-to-many: neither side has unique key values in the relationship column (supported natively in Power BI, but can cause ambiguous or duplicated aggregations if not modeled carefully — often resolved with a bridge table). One-to-one: rare, essentially merges two tables’ grain 1:1.

Q31. What is bidirectional (cross-filter) relationship, and what’s the risk? A: It lets filters flow both directions across a relationship. Risk: ambiguous filter paths in more complex models, circular dependency errors, and performance degradation; Microsoft recommends single-direction filtering by default and only enabling bidirectional where genuinely needed (and being aware of “weak”/many-to-many implications).

Q32. What is a Role-Playing Dimension, and how do you handle it (e.g., OrderDate vs ShipDate)? A: A single dimension (like Date) that logically applies to multiple roles in a fact table. Since Power BI only allows one active relationship between two tables, you create multiple relationships (one active, others inactive) and use USERELATIONSHIP() in DAX to activate the inactive one when needed, or create separate physical/duplicated date tables per role.

Q33. Why do you need a dedicated Date table, and what are the requirements for it to work with time-intelligence functions? A: Built-in auto date/time hierarchies don’t scale well and lack flexibility (custom fiscal years, holidays). A proper Date table must contain one row per day, contiguous dates spanning your data’s full range, and be marked as a “Date Table” (with a genuine Date-typed column) via Model view → Mark as Date Table for functions like TOTALYTD, SAMEPERIODLASTYEAR to work correctly.

Q34. What is the difference between a calculated column, calculated table, and a measure? A: Calculated column: computed row-by-row at data refresh time, stored in the model (uses memory, evaluated in row context). Calculated table: an entire table generated by a DAX expression at refresh time. Measure: computed at query/render time based on filter context — not stored, dynamically recalculated, generally more memory-efficient for aggregations.

Q35. What is Row-Level Security (RLS), and how do you implement it? A: RLS restricts which rows a user can see based on their identity. Implemented via Roles (Modeling → Manage Roles) with DAX filter expressions (e.g., [Region] = USERPRINCIPALNAME() or a lookup against a security table), then assigning users/groups to roles in the Power BI Service (or using dynamic RLS driven by a mapping table).

Q36. What is a snowflake schema, and when might you still use one? A: Dimension tables normalized into multiple related sub-tables (e.g., Product → Subcategory → Category as separate tables instead of one flat Product table). Sometimes used when source systems are already normalized and full denormalization is too costly, though it typically adds more relationships and can hurt performance/simplicity versus a star schema.

5. DAX (Data Analysis Expressions)

Q37. What is the difference between Filter Context and Row Context? A: Row Context exists when DAX iterates row-by-row (e.g., inside calculated columns or iterator functions like SUMX), giving access to each row’s column values. Filter Context is the set of filters applied to a calculation from slicers, visuals, or DAX filter functions — it determines what subset of data a measure aggregates over.

Q38. Explain CALCULATE(). A: CALCULATE(expression, filter1, filter2, …) evaluates an expression within a modified filter context; the filter arguments replace or add to existing filters on the relevant columns. It’s the primary way to override context in DAX and underlies functions like time intelligence internally.

Q39. What’s the difference between FILTER() and using a Boolean condition directly inside CALCULATE()? A: CALCULATE(SUM(Sales[Amount]), Sales[Region]=”West”) is a simple column-predicate filter (fast, uses internal query engine paths). CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 100)) uses FILTER as a table filter, which iterates row-by-row and is generally more expensive — use it only when the filter logic can’t be expressed as a simple column predicate.

Q40. What’s the difference between SUM(), SUMX(), and why would you use one over the other? A: SUM() aggregates a single column directly. SUMX() is an iterator that evaluates an expression per row (row context) then sums the results — needed when the calculation involves multiple columns per row (e.g., SUMX(Sales, Sales[Qty] * Sales[Price])) rather than a pre-existing column.

Q41. Explain ALL(), ALLEXCEPT(), and ALLSELECTED(). A: ALL(Table/Column) removes all filters on the specified table/column, ignoring current context — useful for “% of total” calculations. ALLEXCEPT(Table, Column1, …) removes all filters on a table except the ones specified. ALLSELECTED() removes filters added inside the current visual’s context but retains filters from outside it (e.g., slicers/page filters) — commonly used for “% of parent” visuals that should respect user-selected filters but ignore the visual’s own axis grouping.

Q42. What is context transition, and when does it happen? A: When a row context is converted into an equivalent filter context — this happens automatically when a CALCULATE (or any measure reference, since measures implicitly wrap in CALCULATE) is evaluated inside a row context, e.g., inside SUMX calling a measure. Each row’s column values become single-value filters for that evaluation.

Q43. Give an example of a time-intelligence DAX pattern. A: SalesYTD = TOTALYTD(SUM(Sales[Amount]), ‘Date'[Date]); SalesPriorYear = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(‘Date'[Date])); SalesGrowthPct = DIVIDE([Sales] – [SalesPriorYear], [SalesPriorYear]).

Q44. What is DIVIDE() and why prefer it over the / operator? A: DIVIDE(numerator, denominator, [alternateResult]) safely handles division by zero (returning BLANK or your specified alternate result) without an error, and is generally more performant than wrapping / in an IF to check for zero.

Q45. What’s the difference between implicit and explicit measures? A: Implicit measures are created automatically when you drag a numeric column into a visual and Power BI applies a default aggregation (Sum/Average/etc.) behind the scenes. Explicit measures are DAX formulas you author yourself (e.g., in the modeling pane) — recommended for reusability, consistent business logic, and because implicit measures can’t be referenced by other measures or used in some advanced scenarios.

Q46. What are Variables (VAR/RETURN) used for in DAX, and why use them? A: They store an intermediate expression’s result so it can be reused within the same measure without recalculating it multiple times — improving readability and performance (avoids redundant evaluation).

Q47. Explain the difference between RANKX, TOPN, and using a visual-level Top N filter. A: RANKX computes a rank value per row/category as a measure (usable in tables, conditional formatting). TOPN returns the top N rows of a table expression based on an order-by expression (useful inside other DAX calculations, e.g., “sum of top 5 products”). A visual-level Top N filter is a report-authoring feature that limits what’s displayed in a specific visual, without producing a reusable measure.

6. Visualizations & Reporting

Q48. What is the difference between a Slicer and a Filter pane filter? A: A Slicer is a visual, on-canvas control end users interact with directly. Filter pane filters (Visual/Page/Report level) are configured by the report author and can be locked, hidden, or left interactive; they don’t take canvas space but require opening the pane to see/change.

Q49. What are Bookmarks used for? A: Capturing the current state of a report page (filters, slicer selections, visual visibility, sort order) so it can be recalled via a button — commonly used to build custom navigation, toggle chart views, or create guided “storytelling” tours.

Q50. What is Drillthrough, and how does it differ from Drill Down? A: Drill Down moves within a visual’s own hierarchy (e.g., Year → Quarter → Month) in place. Drillthrough right-click navigates from a summary visual to a separate detail page filtered to the clicked context (e.g., right-click a customer → “Drillthrough to Customer Detail”).

Q51. What is a Custom Visual, and how do you add one? A: A visual not built into the default Power BI palette — sourced from AppSource (community/certified visuals) or imported from a local .pbiviz file. Added via the Visualizations pane → “Get more visuals.”

Q52. What’s the difference between Paginated Reports (RDL) and standard Power BI reports? A: Paginated reports (Power BI Report Builder, .rdl format) are designed for pixel-perfect, print-ready, highly tabular output (invoices, regulatory reports) that can span many pages predictably. Standard Power BI reports are interactive, canvas-based, and optimized for exploration rather than fixed pagination — paginated reports require Premium/Fabric capacity or Report Server to publish to the Service.

7. Performance Optimization

Q53. How do you diagnose a slow report or measure? A: Use Performance Analyzer (Power BI Desktop) to capture per-visual DAX query, visual rendering, and other durations; use DAX Studio to capture and analyze the actual query plan / server timings; check the Query Diagnostics feature in Power Query for slow refresh steps.

Q54. What are common causes of a bloated/slow Power BI model? A: Too many high-cardinality columns (especially unique IDs/GUIDs kept unnecessarily), unnecessary columns loaded from source, bidirectional relationships causing ambiguous or expensive filter propagation, calculated columns that could be measures, unoptimized DAX (row-by-row FILTER where a column predicate would do), and using DirectQuery against a poorly indexed source.

Q55. What is Aggregation (Aggregation tables) in Power BI, and why use them? A: Pre-summarized tables (e.g., daily/monthly sales totals) configured so Power BI automatically redirects queries to the smaller aggregate table when the visual’s grain allows, falling back to the detailed DirectQuery/Import table otherwise — improves performance for very large fact tables, especially in DirectQuery/composite scenarios.

Q56. What is Incremental Refresh, and when should you use it? A: A refresh policy that partitions a table by date range so only recent data (e.g., last N days) is refreshed on each scheduled run, while historical partitions remain untouched — dramatically reducing refresh time for very large fact tables. Requires RangeStart/RangeEnd parameters and is configured in Incremental refresh settings (needs Premium/Fabric capacity for very large partition counts, though basic incremental refresh works on Pro too within limits).

8. Deployment, Governance & Administration

Q57. What is a Workspace, and what’s the difference between a Workspace and an App? A: A Workspace is the collaborative area where developers build/edit reports, datasets, and dashboards. An App is a curated, read-only, published “front door” distributed to a broader audience — separating the messy authoring environment from what end users actually consume.

Q58. What are Deployment Pipelines? A: A Premium/Fabric feature that lets you promote content (reports, datasets, dataflows) through Dev → Test → Prod workspace stages with rule-based parameter swaps (e.g., pointing to different data sources per stage) and a visual diff before deployment.

Q59. How do you schedule a dataset refresh, and what are the limits? A: Dataset Settings → Scheduled Refresh → set frequency/time zone/failure notification emails. Pro workspaces allow up to 8 scheduled refreshes/day; Premium/Fabric capacities allow up to 48/day. An on-premises gateway is required if any source is on-prem or behind a private network.

Q60. What is the difference between a Personal Gateway and an Enterprise/Standard Gateway? A: Personal Gateway is single-user, tied to one Power BI account, and can’t be shared with others for refresh scheduling by other users. The Standard (Enterprise) Gateway supports multiple data sources, multiple users, centralized admin management, and high availability clustering.

Q61. How do you monitor dataset refresh failures or usage across an organization? A: Power BI Admin Portal (tenant-level usage metrics, audit logs), the Power BI REST API (e.g., pulling refresh history programmatically), and built-in “Refresh history” per dataset; larger orgs often centralize this via the Power BI Activity Log / Purview auditing.

Q62. What security options exist beyond Row-Level Security? A: Object-Level Security (OLS, hiding entire tables/columns from certain roles — configured via external tools like Tabular Editor since it’s not in the native Desktop UI), Sensitivity Labels (Microsoft Purview Information Protection integration), workspace-level access roles (Admin/Member/Contributor/Viewer), and dataset “Build” vs “Read” permissions.

9. Scenario-Based / Behavioral-Style Questions

Q63. “You need to combine data from an on-prem SQL Server and a cloud REST API into one report — how do you approach the architecture?” A: Talk through: connecting SQL Server via an on-prem gateway (Import or DirectQuery), pulling the REST API data via Power Query’s Web connector (likely Import, since most APIs don’t support DirectQuery), deciding on a common grain/key to relate the two tables, choosing Import for both if freshness needs allow it (simplifies things and avoids DirectQuery’s cross-source limitations), and setting up a scheduled refresh through the gateway.

Q64. “A DirectQuery report against SQL Server is slow — what do you check first?” A: Check for missing indexes on filter/join columns in the source, look at the generated SQL (via Performance Analyzer/DAX Studio) for inefficient patterns, consider a composite model with a smaller Import “hot” table or aggregation table, review whether bidirectional relationships or complex DAX (row-by-row iterators) are forcing expensive queries, and check network latency between Power BI and the source.

Q65. “Business users say numbers in two reports don’t match — how do you troubleshoot?” A: Compare underlying datasets/semantic models (are the reports on the same dataset or two different ones?), check for differing filter contexts (page/report-level filters, RLS roles applied to the viewer), check refresh timestamps (one may be stale), and verify whether measures use different DAX logic (e.g., one uses ALL for a different scope than the other).

Q66. “How would you design a model that needs to support both near-real-time DirectQuery data and fast historical Import-mode analysis?” A: A Composite model — historical/aggregated data in Import mode for speed, a small “current day/hour” fact table in DirectQuery for freshness, tied together through shared dimension tables, potentially with aggregation tables to auto-route summary-level queries to the fast Import layer.

10. Quick-Fire Terminology Checklist

Tip for the interview: for any data-source question, structure your answer as (1) how you connect, (2) Import vs DirectQuery trade-off for that source, (3) a gotcha specific to that source (e.g., pagination for APIs, query folding for SQL, merged cells for Excel). Interviewers often care more about your reasoning on trade-offs than the exact connector steps.

Power BI Interview Questions & Answers — Mid-Level

A comprehensive guide covering DAX, data modeling, Power Query, visualization, performance, security, and deployment — the core areas mid-level Power BI interviews test.

1. Data Modeling

Q1. What is the difference between a star schema and a snowflake schema? Which does Power BI prefer? A star schema has one central fact table connected directly to denormalized dimension tables. A snowflake schema normalizes dimensions into multiple related tables. Power BI performs best with star schemas because the VertiPaq engine compresses wide, flat dimension tables efficiently and fewer joins mean faster queries and simpler DAX.

Q2. What’s the difference between a fact table and a dimension table? Fact tables hold measurable, quantitative data (sales amount, quantity, transactions) and grow large. Dimension tables hold descriptive attributes (customer, product, date) used to filter and group facts. Fact tables typically have many-to-one relationships pointing to dimension tables.

Q3. Explain relationship cardinality and cross-filter direction. Cardinality (one-to-many, many-to-many, one-to-one) defines how rows in one table relate to rows in another. Cross-filter direction determines whether filters propagate one way (single) or both ways (bidirectional). Single direction is preferred for performance and predictability; bidirectional is used carefully, often for many-to-many bridge tables, since it can cause ambiguity and performance issues.

Q4. Why should you avoid bidirectional relationships when possible? They can introduce ambiguous filter paths, circular dependencies, and slower query performance because Power BI must evaluate filters flowing in both directions across multiple tables. They can also cause unexpected results when combined with other bidirectional relationships in a model.

Q5. What is a role-playing dimension, and how do you handle it in Power BI? A role-playing dimension is one table used in multiple contexts, e.g. a single Date table serving as Order Date, Ship Date, and Due Date. Since Power BI allows only one active relationship at a time between two tables, you create additional inactive relationships and activate them in DAX using USERELATIONSHIP(), or create separate physical/duplicate date tables when you need all roles simultaneously.

Q6. What is a composite model? A composite model combines multiple storage modes (Import, DirectQuery, or Dual) within a single Power BI file, allowing some tables to be imported for speed while others remain live via DirectQuery — useful for blending a large fact table in DirectQuery with smaller imported lookup tables.

Q7. What’s the difference between Import, DirectQuery, and Live Connection storage modes? Import loads data into Power BI’s in-memory engine — fastest performance, but data is only as fresh as the last refresh. DirectQuery sends queries to the source in real time — always current, but performance depends on the source and query complexity. Live Connection is used for Analysis Services/Power BI datasets, where Power BI has no local data and relies entirely on the source’s model.

2. DAX

Q8. What is the difference between a calculated column and a measure? Calculated columns are computed row-by-row during data refresh and stored in the model, consuming memory; they can be used as filters, slicers, or in row/column axes. Measures are calculated on the fly at query time based on filter context and are not stored, making them more efficient for aggregations.

Q9. Explain row context vs. filter context. Row context exists when DAX evaluates an expression one row at a time, such as in calculated columns or inside iterator functions like SUMX. Filter context is the set of filters applied to a calculation from slicers, rows/columns in a visual, or explicit CALCULATE conditions, and it determines what data is visible when a measure is evaluated.

Q10. What does CALCULATE do, and why is it considered the most important DAX function? CALCULATE evaluates an expression within a modified filter context. It’s central because nearly every other filter-manipulation function (FILTER, ALL, USERELATIONSHIP, time intelligence functions) works by being passed as an argument to CALCULATE, which then adjusts the filter context before evaluating the expression.

Q11. What is context transition, and when does it happen? Context transition converts row context into an equivalent filter context. It happens automatically whenever CALCULATE (or any measure reference, since measures implicitly wrap in CALCULATE) is evaluated inside a row context, such as within SUMX or a calculated column — the current row’s values become filters.

Q12. Difference between ALL, ALLEXCEPT, and ALLSELECTED? ALL removes all filters on the specified column(s) or table. ALLEXCEPT removes all filters except those on the specified columns. ALLSELECTED removes filters added by the current visual’s internal context while preserving filters coming from outside the visual (like slicers or page filters) — commonly used for “% of total” calculations that respect user selections.

Q13. What’s the difference between SUM, SUMX, and when would you use SUMX? SUM aggregates a single column directly. SUMX is an iterator that evaluates an expression row-by-row and then sums the results — needed when the calculation itself requires row-level logic first, e.g. SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) to compute revenue that isn’t already a stored column.

Q14. How do you calculate year-to-date, month-to-date, or a rolling 12-month total in DAX? Using time intelligence functions like TOTALYTD, DATESYTD, or DATESINPERIOD combined with CALCULATE, for example: YTD Sales = TOTALYTD(SUM(Sales[Amount]), ‘Date'[Date]) For rolling periods: CALCULATE(SUM(Sales[Amount]), DATESINPERIOD(‘Date'[Date], MAX(‘Date'[Date]), -12, MONTH)) These require a proper, continuous Date table marked as the model’s date table.

Q15. What is the difference between VAR and just writing nested expressions? VAR stores an intermediate calculation result so it can be reused within the same expression without recalculating it, improving readability and often performance, since the engine evaluates the variable once rather than repeating the same subexpression multiple times.

Q16. Explain RELATED vs RELATEDTABLE. RELATED pulls a single related value from the “one” side of a relationship into the “many” side (e.g., getting the Category from Product while in a Sales row). RELATEDTABLE returns a table of related rows from the “many” side when you’re on the “one” side (e.g., returning all Sales rows related to a given Product).

Q17. What are implicit vs explicit measures? Implicit measures are auto-generated when a user drags a numeric column into a visual and Power BI applies a default aggregation (e.g., Sum of Sales). Explicit measures are DAX formulas the developer writes deliberately. Explicit measures are considered best practice because they’re reusable, consistent, and easier to control/optimize.

3. Power Query / Data Transformation

Q18. What language does Power Query use, and what is it fundamentally doing? Power Query uses the M language. Each transformation step (filter, merge, pivot, etc.) generates M code, and the whole query is a sequence of functional steps applied to a data source, producing a transformed table.

Q19. Difference between Merge and Append queries? Merge combines two queries side-by-side based on matching key columns (like a SQL join), adding columns from one table to another. Append stacks queries on top of each other (like a SQL UNION), combining rows from tables with matching structures.

Q20. What is query folding, and why does it matter for performance? Query folding is when Power Query pushes transformation steps back to the source system (e.g., converting steps into a SQL query) instead of processing them locally after extraction. It matters because folded queries let the source database do the heavy lifting, dramatically reducing load time and resource usage — especially important for DirectQuery and large Import refreshes.

Q21. How do you handle a Power Query step that breaks query folding? Identify which step causes the break (right-click a step and check “View Native Query” — if greyed out, folding stopped). Reorder steps so foldable operations (filters, column selection, joins) happen before non-foldable ones (custom M functions, certain data type changes, adding index columns), and avoid transformations the source can’t translate.

Q22. What’s the purpose of parameters in Power Query? Parameters let you externalize values (like a file path, server name, or date range) so queries can be reused or reconfigured without editing the underlying M code directly — useful for switching between dev/test/prod environments or building dynamic, reusable queries.

4. Visualization & Reports

Q23. What is the difference between a slicer and a filter (page/report/visual level)? A slicer is a visible, on-canvas control users can directly interact with. Filters (visual, page, or report level) are typically configured in the Filters pane and may be hidden from end users, applying scope to one visual, an entire page, or the whole report respectively.

Q24. What are bookmarks and how are they typically used? Bookmarks capture the current state of a report page — filter selections, slicer values, visibility of objects — so users can navigate between saved states via buttons or the Bookmarks pane. They’re commonly used to build interactive navigation, toggle between chart views, or create guided “story” experiences.

Q25. What’s the difference between drill-down and drill-through? Drill-down moves between levels of a hierarchy within the same visual (e.g., Year → Quarter → Month). Drill-through navigates from a summary visual to a separate detail page, carrying the selected context (e.g., clicking a customer to jump to a customer detail page) as a filter.

Q26. When would you use a custom visual vs. a native one? Native visuals cover most standard needs (bar, line, matrix, card) and perform well. Custom visuals (from AppSource or custom-coded) are used when a specific need isn’t met natively — e.g., advanced Gantt charts, specific KPI layouts, or specialized statistical visuals — but should be vetted for performance and certification status since uncertified visuals can pose governance/security concerns.

5. Performance Optimization

Q27. What tool would you use to diagnose a slow-loading report, and how? Performance Analyzer (in Power BI Desktop) records the time each visual, DAX query, and visual rendering step takes on refresh/interaction. You’d identify the slowest visual, export its DAX query, and analyze it further in DAX Studio to see query plans and server timings.

Q28. Name three common causes of a slow Power BI report and how you’d address each.

1. Overly complex or numerous visuals on one page — reduce visual count or split into multiple pages.
2. Poorly optimized DAX (e.g., excessive use of FILTER over full table scans, or iterators over large tables) — rewrite using more efficient patterns like CALCULATE with simple filters.
3. A model that isn’t a proper star schema, or bidirectional relationships causing heavy filter propagation — remodel toward a star schema and simplify relationships.

Q29. What is the VertiPaq engine, and why does column cardinality matter? VertiPaq is Power BI’s in-memory columnar storage and compression engine used for Import mode. It compresses each column independently, and compression efficiency depends heavily on cardinality (number of distinct values) — high-cardinality columns (like GUIDs or precise timestamps) compress poorly and bloat the model, so reducing unnecessary granularity (e.g., splitting datetime into date + time, or removing unused unique ID columns) improves size and performance.

Q30. What’s the benefit of disabling auto date/time and building a dedicated Date table? Power BI’s auto date/time feature creates hidden date hierarchy tables behind every date column, which can bloat the model and slow refreshes, especially with many date columns. A single, explicitly built Date table (marked as the official date table) is more efficient, reusable across role-playing scenarios, and gives full control over fiscal calendars, holidays, and custom time intelligence.

6. Security & Governance

Q31. What is Row-Level Security (RLS), and how do you implement it? RLS restricts the data a user can see based on their identity. You create roles in Power BI Desktop, define DAX filter expressions on tables (e.g., [Region] = USERNAME() or a lookup against a mapping table), and assign users to roles in the Power BI Service. The filter automatically propagates through relationships if configured correctly.

Q32. Difference between static RLS and dynamic RLS? Static RLS hardcodes filter conditions per role (e.g., a “West Region” role filtered to Region = “West”), requiring a new role for each segment. Dynamic RLS uses functions like USERPRINCIPALNAME() combined with a security mapping table, so one role’s logic automatically adjusts based on who’s logged in — far more scalable for large user bases.

Q33. What are workspaces, apps, and how do they relate to deployment? Workspaces are collaborative containers in the Power BI Service where reports, datasets, and dashboards are developed and managed with defined access roles (Admin, Member, Contributor, Viewer). Apps are packaged, read-only, published versions of workspace content distributed to a broader audience for consumption, decoupling the development environment from what end users see.

Q34. What is a Deployment Pipeline, and why use one? Deployment Pipelines let you promote content through Dev → Test → Production stages within the Power BI Service, with options to swap data source connections per stage. They support safer, more governed release processes compared to manually republishing files.

7. Refresh & Data Sources

Q35. What is an on-premises data gateway and when is it needed? The gateway is a bridge that allows the Power BI Service (cloud) to securely connect to on-premises or private-network data sources (SQL Server, on-prem files, etc.) for scheduled refresh or DirectQuery, since the cloud service can’t reach those sources directly.

Q36. What’s the difference between scheduled refresh and incremental refresh? Scheduled refresh reloads the entire dataset at defined intervals. Incremental refresh only reloads a defined recent window of data (e.g., last 30 days) while keeping historical partitions intact, dramatically reducing refresh time and resource usage for large fact tables — configured via defined refresh/archive date ranges and parameters.

Q37. How many scheduled refreshes per day does Power BI Pro allow (as of general licensing), and how does Premium differ? Pro-licensed datasets typically support up to 8 scheduled refreshes per day; Premium capacity (or Premium Per User) allows up to 48 refreshes per day, plus supports larger dataset sizes and features like XMLA endpoint access for advanced management.

8. Scenario-Based Questions

Q38. “A stakeholder wants a report to show sales versus last year, but the comparison should only use complete months.” How would you approach this in DAX? You’d build the comparison using SAMEPERIODLASTYEAR or DATEADD against a proper Date table, then add logic to exclude the current, incomplete month — often by comparing MAX(Sales[Date]) against the last day of the month or maintaining a flag column in the Date table marking “complete months,” and filtering both current and prior-year calculations against it.

Q39. “Your dataset takes 40 minutes to refresh and it used to take 10.” How do you investigate? Check whether data volume grew significantly, whether new non-foldable Power Query steps were added, whether new calculated columns or complex DAX were introduced, and whether the source system’s performance changed. Use Power Query’s step-by-step evaluation and query diagnostics, review the model for new high-cardinality columns, and consider incremental refresh if the fact table has grown large.

Q40. “Two departments want to see the same report but only their own data.” What’s the best approach and why? Implement dynamic Row-Level Security with a user-to-department mapping table, rather than building separate reports or duplicating datasets. This keeps a single source of truth, reduces maintenance overhead, and scales automatically as departments or users change.

Q41. “A manager says the total on the report doesn’t match the total in the source system.” How do you troubleshoot? Check for: filters applied at report/page/visual level that aren’t obvious to the viewer; RLS silently restricting data for that user; relationship cardinality/direction causing double-counting or filtering gaps; whether the dataset uses Import (stale data) versus the live source; and whether measures use implicit aggregation versus explicit DAX with different logic than the source query.

Q42. “How would you design a model to support both finance’s fiscal calendar and marketing’s calendar-year reporting?” Build a single Date table containing both fiscal and calendar attributes (Fiscal Year, Fiscal Quarter, Fiscal Month alongside Calendar Year, Quarter, Month), so both teams can build visuals against the same table using whichever hierarchy fits their needs, avoiding duplicate date tables and keeping time intelligence consistent.

9. Rapid-Fire Conceptual Questions

• Q43. What’s the maximum file size for Power BI Pro vs Premium workspaces? Pro workspaces cap datasets around 1 GB; Premium capacities support much larger datasets (historically up to 400 GB depending on SKU/configuration) — always verify current limits, as Microsoft updates these periodically.
• Q44. What is a KPI visual best suited for? Showing a single value’s progress against a target and trend over time at a glance.
• Q45. What does “Dual” storage mode mean? A table can behave as either Import or DirectQuery depending on the query context, useful in composite models for shared dimension tables.
• Q46. What is Power BI Premium’s main advantage over Pro for an enterprise? Capacity-based licensing (not per-user for viewers), larger model/refresh limits, dedicated resources, and features like XMLA endpoints, deployment pipelines, and paginated reports.
• Q47. What does “Show data as a table” or “See records” (right-click on a visual) let a developer do? Quickly inspect underlying detail rows behind an aggregated visual for validation/debugging.

Tips for the Interview Itself

• Be ready to write DAX on a whiteboard/live share — practice CALCULATE, SUMX, time intelligence, and ALLSELECTED from memory.
• Expect at least one modeling design question (star schema, handling many-to-many, role-playing dimensions).
• Have a performance troubleshooting story ready from real experience — interviewers value practical debugging process over textbook definitions.
• Know the difference between “why” and “how” — mid-level interviews often probe why you’d choose one approach (e.g., Import vs DirectQuery) over just knowing the syntax.

Power BI Senior-Level Interview Questions & Answers

A comprehensive guide covering data modeling, DAX, Power Query (M), performance tuning, architecture, governance, and real-world scenarios — the depth expected at Senior / Lead Power BI Developer or Power BI Architect level.

1. Data Modeling

Q1. What is a star schema and why is it preferred over a snowflake schema in Power BI? A star schema has a central fact table connected directly to denormalized dimension tables via single-column keys. It’s preferred because VertiPaq (Power BI’s in-memory engine) is optimized for fewer, wider dimension tables and simpler join paths, which reduces the number of relationships DAX has to traverse, improves compression, and simplifies filter propagation. Snowflake schemas (normalized dimensions split into sub-dimensions) add extra joins, increase query complexity, and can hurt performance and usability for report authors.

Q2. Explain the difference between a single-directional and bi-directional relationship, and when bi-directional filtering is appropriate. Single-directional relationships propagate filters from the “one” side (dimension) to the “many” side (fact) only. Bi-directional relationships also propagate filters back from fact to dimension, which can cause ambiguous filter paths, circular dependencies, and performance issues in complex models. It’s appropriate in specific cases like many-to-many bridge tables, or when you deliberately need cross-filtering between two dimension tables (e.g., a “sales by region” slicer affecting “budget by region”). Best practice: use it sparingly, and prefer CROSSFILTER() in DAX for a controlled, measure-level alternative instead of setting bi-directional globally.

Q3. How do you model many-to-many relationships in Power BI, and what are the performance implications? Typically through a bridge table (a distinct list of the many-to-many key) connecting two fact-like tables, or by using a composite key relationship where the relationship cardinality is set to many-to-many directly (supported natively since 2018). Performance implication: many-to-many relationships bypass some VertiPaq optimizations and can be significantly slower because they can’t rely on a straightforward single-value join; they often require additional DAX (e.g., TREATAS) to control filter behavior efficiently.

Q4. What is a composite model, and what are its use cases and limitations? A composite model combines multiple data connection modes (Import, DirectQuery, or Dual) within a single Power BI model, allowing tables from different sources or storage modes to coexist. Use cases: combining a large DirectQuery fact table from a data warehouse with smaller Import dimension tables for performance, or blending on-prem and cloud sources. Limitations: relationships between DirectQuery and Import tables can trigger “limited relationships,” query folding may break, and there’s added complexity in managing storage mode per table (Dual mode tables can serve both contexts depending on the query).

Q5. What is Dual storage mode and why would you use it? Dual mode allows a table to behave as either Import or DirectQuery depending on the query context — if the query only needs cached data, it uses Import; if it must join with a DirectQuery table, it switches to DirectQuery. It’s typically used for dimension tables in composite models, avoiding duplicate DirectQuery hits while still allowing joins with live fact tables.

Q6. How do you handle role-playing dimensions in Power BI (e.g., a single Date dimension used for Order Date, Ship Date, Due Date)? Since Power BI doesn’t support multiple active relationships to the same table directly, you either (a) create separate physical date tables for each role via CALCULATETABLE/duplicated tables, or (b) keep one Date table with one active relationship and create inactive relationships for the others, activating them in specific measures using USERELATIONSHIP().

Q7. What’s the difference between calculated columns and calculated tables, and when should you avoid calculated columns? Calculated columns are computed row-by-row at data refresh and stored in the model (consuming memory, not compressed as well as native columns since VertiPaq applies compression to raw data differently for computed columns). Calculated tables are entirely new tables materialized via DAX. Avoid calculated columns when the same logic can be pushed upstream into Power Query or the source SQL — this reduces model size and refresh time, and improves compression, since Query-level transformations often compress better than DAX-computed columns.

2. DAX — Advanced Concepts

Q8. Explain filter context vs. row context, and how they interact. Row context exists when iterating row-by-row (e.g., inside SUMX, calculated columns) — it means “the current row.” Filter context is the set of filters applied to the model at the time a calculation is evaluated (from slicers, visuals, or explicit CALCULATE filters). Row context does not automatically become filter context; that’s why functions like SUMX need explicit column references, and why CALCULATE can convert row context into filter context implicitly when used inside an iterator.

Q9. What does CALCULATE actually do, and what is context transition? CALCULATE modifies the filter context for the expression it wraps, applying new filters (or removing them via ALL/REMOVEFILTERS). Context transition happens when CALCULATE is invoked inside a row context (e.g., inside SUMX or a calculated column) — it converts the current row context into an equivalent filter context, effectively filtering the model to that single row’s values, which is essential for measures like running totals or comparisons at the row level.

Q10. Explain the difference between ALL, ALLEXCEPT, ALLSELECTED, and REMOVEFILTERS.

• ALL(table/column) — removes all filters on the specified table or column entirely, ignoring even what’s on a report page or slicer.
• ALLEXCEPT(table, column1, column2…) — removes all filters on a table except the ones specified, useful for “unfilter everything except date.”
• ALLSELECTED() — removes filters added by the current visual’s internal context but retains external filters applied by slicers/other visuals outside the visual — used for calculations like “% of selected total.”
• REMOVEFILTERS() — functionally similar to ALL used inside CALCULATE, introduced as more readable syntax to explicitly remove filters without ambiguity about column vs. table behavior.

Q11. How would you write a DAX measure to calculate Year-over-Year growth, and what functions would you use?

YoY Growth % =

VAR CurrentSales = [Total Sales]

VAR PriorYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(‘Date'[Date]))

RETURN DIVIDE(CurrentSales – PriorYearSales, PriorYearSales)

Key functions: SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD, or manual CALCULATE with a shifted date filter. DIVIDE() is preferred over / to gracefully handle division-by-zero.

Q12. What is the difference between DATEADD and SAMEPERIODLASTYEAR, and when would you use PARALLELPERIOD? SAMEPERIODLASTYEAR is shorthand for DATEADD(dates, -1, YEAR). DATEADD is more flexible, allowing any interval shift (months, quarters, days). PARALLELPERIOD returns full periods (e.g., entire prior month) rather than a day-for-day shift, useful when you want period-level comparison regardless of the exact day alignment.

Q13. Explain the difference between implicit and explicit measures, and why senior developers avoid relying on implicit ones. Implicit measures are auto-generated aggregations when you drag a numeric column into a visual (e.g., Sum of Sales). Explicit measures are DAX-defined (Total Sales = SUM(Sales[Amount])). Senior developers avoid implicit measures because they can’t be reused across visuals consistently, don’t support advanced logic, don’t work well with certain tools like measure-level security or third-party visuals, and can silently behave inconsistently (e.g., defaulting to a different aggregation when a field is reused).

Q14. What are iterator functions and how do they differ from their non-iterator counterparts (e.g., SUMX vs SUM)? Iterators (SUMX, AVERAGEX, MAXX, RANKX, FILTER) evaluate an expression row-by-row within a given table, then aggregate the results — enabling row-level logic (e.g., quantity × price per row, then summed). Non-iterators (SUM, AVERAGE) operate directly on a single column without an expression, applying the aggregation over whatever filter context is active.

Q15. How does VARIABLE (VAR) usage improve DAX performance and readability? Variables materialize an expression’s result once, preventing DAX from re-evaluating the same subexpression multiple times within a formula. This avoids redundant context transitions/query plan branches, improves readability, and often significantly reduces query time in complex nested measures.

Q16. Explain what a “virtual relationship” is and how TREATAS is used to build one. A virtual relationship is a filter propagation created at query time via DAX rather than a defined model relationship — commonly used for many-to-many scenarios or when relationships can’t be physically modeled (e.g., disconnected tables). TREATAS(table, columns…) applies the values of one table as if they were the values of specified columns in another, letting you filter table A by values derived from table B without a physical join.

Q17. What is the difference between RANKX behavior with DENSE vs default ranking, and how do ties get handled? By default, RANKX assigns the same rank to tied values but skips subsequent rank numbers (like SQL RANK()). With the DENSE parameter, ties get the same rank but subsequent ranks are not skipped (like DENSE_RANK() in SQL) — useful when you want consecutive rank numbers regardless of ties.

Q18. How do you optimize a slow DAX measure? Walk through your diagnostic approach.

1. Use Performance Analyzer in Power BI Desktop to isolate which visual/query is slow.
2. Export the query and analyze it in DAX Studio, checking the Server Timings tab to separate time spent in the Formula Engine (FE) vs Storage Engine (SE) — high FE time usually indicates inefficient DAX logic (e.g., excessive context transition, nested iterators), while high SE time may point to model/schema issues.
3. Check for unnecessary use of FILTER() over full tables instead of using column-level filters directly in CALCULATE.
4. Replace calculated columns with Power Query transformations where possible.
5. Consider variables to avoid redundant evaluation, and check for accidental bi-directional relationships causing filter fan-out.
6. Review cardinality of relationship columns and reduce column cardinality (e.g., split datetime into date + time) to improve compression and query speed.

3. Power Query (M Language)

Q19. What is query folding, and why is it critical for performance? Query folding is when Power Query translates its transformation steps into a single native query pushed down to the source system (e.g., SQL) rather than pulling all raw data and transforming it locally. It’s critical because it minimizes data transfer and leverages the source engine’s optimized processing. Certain steps (custom M functions, merging with non-foldable sources, adding index columns) break folding, forcing Power BI to process data locally, which slows refreshes significantly on large datasets.

Q20. How do you check whether a query is folding, and what breaks it? Right-click a step and check if “View Native Query” is enabled (if grayed out, folding has stopped at or before that step). Common fold-breakers: Table.Buffer, custom M functions without native equivalents, certain fuzzy merges, changing column data types in an unsupported way for the source, and merges across different source types.

Q21. Explain the difference between Table.Buffer and query folding, and a legitimate use case for buffering. Table.Buffer forces a table into memory at a specific point, breaking folding but ensuring a stable snapshot for subsequent steps (useful to avoid inconsistent results when a source table changes mid-refresh, or to improve performance when the same table is referenced multiple times downstream, avoiding repeated source hits).

Q22. What’s the difference between merging queries and appending queries? Merge is a join operation (like SQL JOIN) — combining columns from two queries based on matching key(s). Append stacks rows from multiple queries with matching schemas into a single table (like SQL UNION).

Q23. How would you handle incremental refresh in Power BI, and what are the requirements? Incremental refresh partitions a table by date ranges so only recent data is refreshed rather than the whole table. Requirements: a RangeStart/RangeEnd parameter pair, a date/datetime column to filter on, and (for Import mode) Premium/PPU capacity or the appropriate license for large-scale use. You define the refresh policy (e.g., “refresh last 5 days, keep 3 years”) in the table’s Incremental Refresh settings. For DirectQuery hybrid tables, you can also enable “Get only the latest data” combined with real-time queries — this is the Hybrid Tables feature.

Q24. How do you parameterize a Power Query source for dev/test/prod environments? Create Power Query parameters (e.g., ServerName, DatabaseName) and reference them in the source step instead of hardcoded values. Combined with deployment pipelines, parameter values can differ across dev/test/prod workspaces, or be swapped via Tabular Editor / XMLA endpoint scripting for automated CI/CD.

4. Performance Optimization & Architecture

Q25. Explain VertiPaq’s compression model and why column cardinality matters. VertiPaq (the Analysis Services in-memory columnar engine used by Power BI) stores data column-by-column using dictionary encoding and run-length encoding (RLE). Low-cardinality columns compress extremely well (fewer unique values = smaller dictionary, longer RLE runs); high-cardinality columns (e.g., unique IDs, timestamps down to the second) compress poorly and bloat model size. This is why best practice is to split datetime columns into Date + Time, avoid storing unnecessary unique text/GUID columns, and aggregate data where full granularity isn’t required.

Q26. What are aggregation tables and how do they improve performance in large DirectQuery models? Aggregation tables are pre-summarized Import-mode tables (e.g., sales aggregated by day/region) sitting “in front of” a large DirectQuery fact table. Power BI automatically routes queries to the aggregation table when the requested granularity matches, falling back to DirectQuery for detail-level queries — dramatically improving performance for high-level dashboard queries while retaining full drill-through capability.

Q27. How do Premium capacity and Premium Per User (PPU) differ from Pro, and why would an enterprise choose Premium? Pro is per-user licensing with sharing limited to other Pro users, and includes standard refresh/storage limits (e.g., 8 refreshes/day, 1GB dataset size back then — now higher with Large Dataset Storage Format). Premium (capacity-based) and PPU unlock: larger model sizes, XMLA endpoint read/write access, deployment pipelines, incremental refresh at scale, paginated reports, AI features, dataflows with enhanced compute, and the ability to share content with users who don’t have individual Pro licenses (Premium capacity only). Enterprises choose Premium for governance at scale, performance isolation (dedicated capacity vs shared), and cost efficiency at high user counts.

Q28. What is the XMLA endpoint, and what enterprise scenarios does it unlock? The XMLA endpoint exposes a Power BI dataset like a traditional Analysis Services tabular model, enabling external tools (Tabular Editor, DAX Studio, SSMS, ALM Toolkit) to connect for advanced model editing, scripted deployments, metadata-driven automation, and integrating Power BI datasets into broader BI/CI-CD pipelines. Requires Premium/PPU/Fabric capacity.

Q29. What are Power BI Dataflows, and how do they differ from datasets? Dataflows are reusable ETL layers built with Power Query Online, stored in Azure Data Lake Storage (CDM format), decoupled from any single report/dataset. They let multiple datasets/reports reuse the same cleaned, transformed data without duplicating logic, and support enhanced compute engine for faster joins/transformations at scale — essentially a “self-service data warehouse” layer within Power BI.

Q30. Describe the components involved in an enterprise Power BI deployment architecture (on-prem to cloud). Typically: source systems (SQL Server, SAP, Oracle, etc.) → On-premises Data Gateway (for DirectQuery/scheduled refresh against on-prem sources) → Power BI Dataflows/Datasets in the Service → Workspaces organized by business domain, governed by Premium/Fabric capacity → Deployment Pipelines (Dev/Test/Prod) → Apps for distribution → Row-Level Security enforced per workspace/dataset → Monitoring via the Power BI Admin Portal, Usage Metrics, and Azure Log Analytics integration for auditing.

Q31. When would you use a Live Connection vs Import vs DirectQuery, and what are the trade-offs?

• Import: fastest query performance (in-memory), but data is only as fresh as the last refresh, and subject to model size limits.
• DirectQuery: real-time data, no duplication, but query performance depends entirely on the source system, and DAX capability is more limited (some functions restricted/behave differently).
• Live Connection (to Analysis Services/Power BI datasets): no local model; you connect directly to an existing semantic model, useful for enforcing a single certified dataset (“one version of the truth”) across many reports, but you can’t add new relationships or measures outside what the connected model exposes (unless using DAX query view or composite models with Analysis Services in Premium).

5. Security & Governance

Q32. How do you implement Row-Level Security (RLS) in Power BI, including dynamic RLS? Static RLS: define roles in Power BI Desktop (Modeling > Manage Roles) with DAX filter expressions on tables (e.g., [Region] = “West”), then assign users to roles in the Service. Dynamic RLS: use USERPRINCIPALNAME() or USERNAME() combined with a mapping table (User-to-Region) and a DAX filter like:

[Region] = LOOKUPVALUE(UserRegionMapping[Region], UserRegionMapping[UserEmail], USERPRINCIPALNAME())

This way, one role dynamically filters data per logged-in user without needing a separate role per user.

Q33. What is Object-Level Security (OLS) and how does it differ from RLS? OLS restricts visibility of entire tables or columns (e.g., hiding a Salary column from certain roles) regardless of row values, whereas RLS filters which rows a user can see within visible tables/columns. OLS is configured via external tools like Tabular Editor (not natively in Power BI Desktop UI) by setting permissions on table/column metadata per role.

Q34. How would you design a governance strategy for a large organization with hundreds of Power BI reports? Key elements: a Center of Excellence (COE) for standards and training; workspace structure aligned to business domains with clear owner/contributor/viewer roles; certified/endorsed datasets to establish “single source of truth”; deployment pipelines for controlled promotion Dev→Test→Prod; sensitivity labels integrated with Microsoft Purview/Information Protection; usage monitoring via Admin APIs and Log Analytics; naming conventions and a shared style guide; and periodic content audits to retire unused/duplicate reports (using Usage Metrics and the Admin Portal’s inventory).

Q35. How does sensitivity labeling and data loss prevention (DLP) work with Power BI? Sensitivity labels (from Microsoft Purview Information Protection) can be applied to datasets, reports, and dashboards to classify data (e.g., Confidential, Highly Confidential), with labels flowing downstream into exported files (Excel, PDF) to maintain protection. DLP policies can then be configured to detect and act on labeled or pattern-matched sensitive data (e.g., credit card numbers) being shared inappropriately, triggering alerts or blocking actions.

Q36. What’s the difference between a workspace and an app, and how does content distribution typically work in an enterprise? A workspace is the collaborative development environment where content is built and permissions are granted for editing. An App is a curated, read-only distribution layer published from a workspace to a broader audience, allowing versioned publishing (so consumers only see finalized updates, not work-in-progress) and simplified permission management (via Microsoft 365 Groups or security groups) separate from workspace edit access.

6. Integration & Enterprise Scenarios

Q37. How does Power BI integrate with Azure Synapse Analytics or Microsoft Fabric, and why does this matter architecturally? Power BI can connect via DirectQuery/Import to Synapse SQL pools or Fabric’s OneLake/Lakehouse/Warehouse. With Microsoft Fabric specifically, Power BI datasets can leverage Direct Lake mode — reading Delta tables directly from OneLake without a traditional import/refresh cycle, combining near-Import performance with near-real-time freshness. Architecturally, this reduces data duplication (single copy of data in OneLake serving multiple engines: Spark, SQL, Power BI) and simplifies the modern data estate.

Q38. How would you approach migrating a legacy SSRS/SSAS reporting environment to Power BI? Assessment phase (inventory reports, usage, and complexity), then: migrate SSAS Multidimensional/Tabular models via compatible tools (or rebuild as Power BI datasets, given data volume/DAX considerations), convert SSRS paginated reports to Power BI Paginated Reports where pixel-perfect layouts are required, rebuild ad hoc/interactive reports as native Power BI reports, and run a phased pilot with a business unit before full rollout, validating calculations against legacy outputs for parity.

Q39. Describe how you’d set up CI/CD for Power BI using Azure DevOps or GitHub, and what tools are involved. Use Power BI Desktop Projects (PBIP) format (or Tabular Model Definition Language for datasets) to store reports/datasets as version-controllable text-based files rather than binary .pbix, integrate with Git for source control, use Tabular Editor and ALM Toolkit (or tabular-editor CLI) to script schema comparisons and deployment, orchestrate via Azure DevOps Pipelines/GitHub Actions calling the Power BI REST API or XMLA endpoint to deploy across Dev/Test/Prod workspaces (often paired with Deployment Pipelines for the UI-driven promotion path), and include automated testing (e.g., validating DAX query results against expected values) before promotion.

Q40. How do you monitor and troubleshoot capacity performance issues on a Power BI Premium/Fabric capacity? Use the Premium Capacity Metrics app (or Fabric Capacity Metrics app) to monitor CPU utilization, memory, and throttling events over time; identify overloaded/oversized datasets consuming excessive resources; use the Admin Portal to review workspace assignments and rebalance; leverage Log Analytics integration for detailed query-level diagnostics; and consider scaling capacity SKU, splitting workloads across multiple capacities, or optimizing offending datasets/reports (large models, inefficient DAX, excessive refresh frequency).

7. Scenario & Behavioral Questions

Q41. “A stakeholder says the numbers in Power BI don’t match the source system. How do you investigate?” Check refresh timestamps first (is the model stale?), verify filter context in the visual (are slicers/RLS silently limiting scope?), compare the DAX logic against business definitions (e.g., does “Total Sales” include tax, returns, in-progress orders the same way the source report does?), check for duplicate rows from a broken merge/relationship (many-to-many join fan-out), and validate at the query level using DAX Studio against a known source query. Document the root cause and correct the measure/model, not just the number.

Q42. “You inherited a Power BI model with 50+ measures and no documentation, running slowly. How do you approach fixing it?” Use Tabular Editor’s Best Practice Analyzer and DAX Studio’s VertiPaq Analyzer to get an objective picture of model size, column cardinality, and unused columns/measures. Use Performance Analyzer to identify the worst-performing visuals. Prioritize fixes by impact: remove unused columns, fix inefficient relationships (bi-directional overuse), replace calculated columns with Power Query steps, consolidate duplicate measures, and document logic as you go (measure descriptions, a model documentation export). Communicate findings and a remediation plan to stakeholders before large-scale changes, since business users depend on it in production.

Q43. “How do you decide between building a single large enterprise semantic model vs several smaller domain-specific models?” A single enterprise model promotes consistency (“one version of the truth”) and reduces duplicate logic, but can become a bottleneck for governance, refresh times, and agility if every team must request changes centrally. Smaller domain models allow faster iteration and clearer ownership but risk inconsistent metric definitions across teams. In practice, a hybrid approach works well: a certified core enterprise model for shared dimensions/metrics, with domain teams building on top via Live Connection or composite models, governed by a COE that manages the core model’s change process.

Q44. “How would you explain a complex DAX measure to a non-technical business stakeholder?” Avoid syntax; describe the business logic in plain terms — what it calculates, what it includes/excludes, and any edge cases (e.g., “this shows year-over-year growth, comparing this year’s sales to the same period last year, only for completed orders”). Use a simple example with real numbers to illustrate, and offer to walk through the actual DAX with anyone technical who wants the details.

Q45. “Tell me about a time you had to balance a business request against a performance or governance constraint.” (Structure with STAR: Situation, Task, Action, Result.) A strong answer shows negotiating trade-offs — for example, a stakeholder wanting real-time row-level detail across millions of records, resolved by proposing an aggregation-table hybrid so dashboards stay fast while a drill-through page still supports on-demand DirectQuery detail, satisfying both performance and business need.

Quick-Reference: Topics Senior Interviewers Often Probe Deeper On

• Filter context propagation across bi-directional and many-to-many relationships
• Storage Engine vs Formula Engine time in DAX Studio Server Timings
• Query folding boundaries in complex Power Query pipelines
• Direct Lake mode vs Import vs DirectQuery trade-offs in Microsoft Fabric
• Deployment pipeline and XMLA-based CI/CD design
• Dynamic RLS with multiple role hierarchies
• Capacity planning and cost governance across Premium/Fabric SKUs
• Data model documentation and change management at scale

Tip for interview prep: Be ready to whiteboard a star schema for a real scenario the interviewer gives you (e.g., “model a subscription billing system”), and be ready to write a DAX measure live — YoY growth, running total, and RANKX with ties are the most commonly requested.