Power BI interviews have a specific texture that trips up people coming from a pure BI-tool background: it’s not just “can you build a nice-looking report,” it’s “do you understand the data model underneath it well enough to know why a measure is returning the wrong number, or why a report is slow.” Anyone can drag fields onto a canvas. Fewer people can explain exactly why a DAX measure behaves differently inside a visual with a different filter context.
Split into Beginner (1–30), Intermediate (31–70), and Advanced (71–100), every answer leans on the reasoning behind Power BI’s behavior — the data model, the filter context, the engine underneath — rather than a menu-click description, since that’s what actually separates a strong candidate once the conversation moves past the basics.
One framing worth carrying through the whole list: nearly every “why is my number wrong” question in Power BI comes down to filter context — what’s actually being filtered, in what order, by what — and almost every strong answer in this list traces back to that same idea in some form.
Beginner Level
1. What is Power BI, and what are its main components? A business intelligence and data visualization tool from Microsoft. Its main components are Power BI Desktop (where you build reports and data models), the Power BI Service (the cloud platform for publishing, sharing, and collaborating on reports), Power BI Mobile (viewing reports on phones/tablets), and the Power BI Gateway (connecting cloud reports to on-prem data sources).
2. What’s the difference between Power BI Desktop and Power BI Service? Power BI Desktop is the free, local application where you connect to data, build the data model, write DAX measures, and design report visuals. Power BI Service is the cloud-based platform where published reports live, get shared with others, refreshed on a schedule, and organized into workspaces and apps — Desktop is where you build, Service is where you publish and collaborate.
3. What is Power Query, and what is it used for? Power BI’s data connection and transformation engine, using a language called M under the hood — it’s where you connect to your data sources and clean, reshape, and combine data before it ever reaches the data model, all through a largely visual, step-by-step interface called the Power Query Editor.
4. What is DAX, and what is it used for? Data Analysis Expressions — the formula language used to create calculated columns, calculated tables, and measures in Power BI’s data model. It’s used for defining custom calculations and aggregations that aren’t already directly present in your source data.
5. What’s the difference between a calculated column and a measure? A calculated column is computed row by row and physically stored in the data model, taking up memory, and its value is fixed per row regardless of filters applied in a visual. A measure is calculated dynamically at query time, based on whatever filter context is currently applied in the visual it’s used in, and doesn’t take up storage the way a calculated column does.
6. What is a data model in Power BI? The structure of tables and the relationships between them that Power BI uses to answer queries — typically following a star schema pattern with fact tables (containing measures/transactions) connected to dimension tables (containing descriptive attributes like dates, products, or customers).
7. What is a relationship, and what are the main types? A defined connection between two tables based on a common column, letting Power BI correctly filter and aggregate data across tables. The main cardinality types are one-to-many (most common, like one customer to many orders), one-to-one (less common), and many-to-many (support added later, used more cautiously due to added complexity).
8. What is a star schema, and why is it recommended for Power BI models? A data modeling pattern with a central fact table (containing measurable events, like sales transactions) surrounded by dimension tables (containing descriptive context, like date, product, customer) connected directly to it. It’s recommended because it’s simpler for the engine to optimize, easier for DAX to reason about, and generally performs and behaves more predictably than a more normalized, deeply nested schema (a “snowflake” pattern).
9. What’s the difference between a fact table and a dimension table? A fact table holds quantitative, measurable data — sales amounts, order quantities, transaction counts — typically with foreign keys linking to dimension tables. A dimension table holds descriptive, categorical attributes — product names, customer details, date attributes — used to filter, group, and provide context for the facts.
10. What are the different visualization types available in Power BI, and how do you choose which to use? Bar/column charts (comparing categories), line charts (trends over time), pie/donut charts (part-to-whole, best for few categories), tables/matrices (detailed, precise values), maps (geographic data), cards (single key metrics), and many more. The choice depends on what you’re trying to communicate — comparison, trend, composition, or relationship — rather than picking whichever visual looks most visually interesting.
11. What is a slicer, and how is it different from a filter? A slicer is a visual, on-canvas control that lets report users interactively filter the report by clicking or selecting values — visible and directly interactive. A filter (in the Filters pane) also restricts data, but is typically configured by the report author and, depending on its level (visual, page, or report), may or may not be directly visible or adjustable by the end user in the same interactive way a slicer is.
12. What’s the difference between filtering at the visual, page, and report level? A visual-level filter applies only to one specific visual. A page-level filter applies to every visual on that specific report page. A report-level filter applies across every page in the entire report — choosing the right level avoids either redundantly configuring the same filter repeatedly or accidentally filtering something that should remain visible elsewhere.
13. What is a bookmark in Power BI, and what’s a common use case? A saved snapshot of a report page’s current state — filter selections, visual visibility, slicer values — that can be recalled later, often via a button. A common use case is building a “guided” navigation experience or toggling between different views of the same page (like switching between a chart view and a table view) without needing separate pages.
14. What is Power BI’s Get Data feature, and what kinds of sources can it connect to? The feature (and dialog) used to connect Power BI to a data source — supporting a very wide range including Excel files, CSV files, SQL Server and other databases, SharePoint, web APIs, cloud services like Azure SQL and Salesforce, and many more, each with its own specific connector.
15. What’s the difference between Import mode and DirectQuery mode? Import mode loads a copy of the data directly into Power BI’s in-memory model, offering the fastest query performance but requiring a scheduled refresh to stay current. DirectQuery mode leaves the data in the source system and sends live queries to it on demand, keeping data always current but generally with slower report performance, since every interaction triggers a live query back to the source.
16. What is a Power BI workspace, and what’s it used for? A container in the Power BI Service for organizing and collaborating on related reports, dashboards, and datasets — typically representing a team, department, or project, with its own membership and permission settings controlling who can view or edit its contents.
17. What’s the difference between a report and a dashboard in Power BI? A report is a multi-page, detailed collection of interactive visuals built from a single underlying dataset, allowing deep filtering and exploration. A dashboard is a single-page collection of pinned visuals (called tiles), which can be pulled from multiple different reports and datasets, giving a consolidated, high-level view but with less interactive depth than the original reports.
18. What is Power BI Gateway, and when do you need one? A bridge application installed on-prem (or on a VM) that lets the cloud-based Power BI Service securely connect to and refresh data from on-prem data sources — needed whenever your data lives somewhere the cloud service can’t reach directly, like an on-prem SQL Server database.
19. What is a hierarchy in Power BI, and why would you create one? A grouped, nested arrangement of related fields (like Year > Quarter > Month > Day, or Country > State > City) that lets users drill up and down through levels of detail within a single visual, rather than needing separate visuals or fields for each level of granularity.
20. What’s the difference between SUM and SUMX in DAX, at a basic level? SUM directly aggregates a single existing numeric column. SUMX is an iterator function that evaluates an expression row by row across a table and then sums the results — needed when the value you want to sum isn’t a simple existing column but has to be calculated per row first (like quantity times price, where that product isn’t already a stored column).
21. What is the Power BI Report Server, and how is it different from the Power BI Service? An on-premises alternative to the cloud-based Power BI Service, letting organizations host and share Power BI reports entirely within their own infrastructure — chosen specifically when data residency, regulatory, or connectivity requirements prevent using the cloud service.
22. What is a KPI visual in Power BI? A visual specifically designed to show a key metric’s current value against a target, along with a trend indicator — a compact, purpose-built way to communicate “are we on track” at a glance, rather than requiring a full chart to convey the same basic status.
23. What’s the difference between refreshing a dataset manually versus setting up a scheduled refresh? A manual refresh is triggered on demand by a user in the Power BI Service. A scheduled refresh runs automatically at defined times (up to a certain frequency depending on license type), keeping an Import-mode dataset’s data current without anyone needing to remember to refresh it themselves.
24. What is Power BI’s “Publish to Web” feature, and what’s an important consideration before using it? A feature that generates a public, embeddable link to a report, viewable by anyone with the link, without requiring a Power BI license or login. The important consideration: it makes the report’s data genuinely public on the internet, so it should never be used for anything containing sensitive or confidential information.
25. What is conditional formatting in Power BI, and how is it typically used? A feature letting you dynamically change a visual’s appearance (background color, font color, data bars, icons) based on the underlying data’s values — commonly used to highlight values above or below a threshold, or to create a heat-map effect across a table or matrix, making patterns visually immediate rather than requiring the viewer to read every number.
26. What’s the difference between a table visual and a matrix visual? A table visual displays data in a simple flat grid, similar to a spreadsheet, with no row grouping/pivoting. A matrix visual supports row and column grouping (similar to a pivot table), letting you nest hierarchies and see subtotals across both dimensions, which a simple table doesn’t support.
27. What is Power BI’s Q&A feature? A natural-language query feature letting users type a question in plain English (like “total sales by region last year”) and have Power BI automatically generate a visual answering it, without needing to manually build the visual themselves.
28. What’s the purpose of the Fields pane in Power BI Desktop? It lists every table and column/measure available in the current data model, letting you drag fields directly onto the report canvas to build visuals, or reference them while writing DAX formulas — essentially your map of everything currently available in the model.
29. What is a custom visual in Power BI, and where do you get one? A visual type beyond Power BI’s built-in set, created by Microsoft, a partner, or the community, importable from the AppSource marketplace (or a local file) to extend the range of available visualization types beyond the defaults.
30. What’s a simple way to explain why report performance matters, and what’s one basic thing a beginner can do to help it? A slow report frustrates users and reduces adoption — nobody wants to wait 30 seconds for a page to render. One basic thing a beginner can do is remove unused columns and tables from the model rather than importing everything from a source “just in case,” since a smaller model is generally a faster one.
Intermediate Level
31. Explain filter context and row context in DAX, and why the distinction matters. Row context is the “current row” a calculation is being evaluated for — relevant in calculated columns and inside iterator functions like SUMX, where the formula naturally has access to each row’s values one at a time. Filter context is the set of filters currently applied to a calculation from slicers, visual axes, or explicit FILTER/CALCULATE logic — relevant to measures, which are recalculated based on whatever filter context surrounds them in a given visual. The distinction matters because a formula’s actual result can differ completely depending on which context it’s evaluated under, and most DAX confusion traces back to not correctly tracking which context applies where.
32. What does the CALCULATE function do, and why is it considered the most important function in DAX? CALCULATE evaluates an expression within a modified filter context — it can add, replace, or remove filters compared to whatever context already exists. It’s considered the most important DAX function because it’s the primary mechanism for deliberately changing filter context, which underlies the vast majority of non-trivial DAX calculations, from year-over-year comparisons to percentage-of-total calculations.
33. What is context transition, and when does it happen? Context transition is what happens when a row context is converted into an equivalent filter context — this occurs automatically whenever CALCULATE (or CALCULATETABLE) is used inside a row context, such as within a calculated column or inside an iterator function. It’s a subtle but important behavior, since it means a measure referenced inside a row context doesn’t just use that row’s raw value — it gets filtered as if a filter had been explicitly applied for that row’s specific values.
34. What’s the difference between FILTER and simply using a Boolean condition directly inside CALCULATE? A simple Boolean condition inside CALCULATE (like CALCULATE([Sales], Product[Category] = "Bikes")) is a shorthand the engine translates internally, generally efficient and preferred for straightforward single-column conditions. FILTER is a full table-returning function that iterates row by row, needed for more complex logic that can’t be expressed as a simple column comparison — but it’s generally more expensive, so it should be reserved for cases the simple syntax genuinely can’t handle.
35. What’s the difference between RELATED and RELATEDTABLE? RELATED pulls a single related value from the “one” side of a one-to-many relationship into the “many” side’s row context — used in a calculated column on the many-side table. RELATEDTABLE returns an entire related table of rows from the “many” side, used from the “one” side’s row context — they’re essentially mirror images of each other, depending on which direction across the relationship you’re pulling data.
36. What’s the difference between Import mode, DirectQuery, and Composite models? Import loads data into memory for fast performance but requires refresh to stay current. DirectQuery queries the source live, staying current but with generally slower performance. A Composite model combines both within a single data model — some tables imported, others in DirectQuery — letting you balance performance and freshness on a table-by-table basis, useful when most of your model can be safely imported but one specific table needs to reflect near-real-time data.
37. What is a bidirectional (both-direction) relationship filter, and what’s the risk of using it too liberally? By default, filters flow one direction across a relationship (from the “one” side to the “many” side). A bidirectional relationship lets filters flow both ways — useful for specific scenarios like many-to-many relationships through a bridge table, but overusing it broadly across a model risks ambiguous filter paths and can create circular filtering behavior that makes measures behave unpredictably, which is why it’s generally recommended only where genuinely needed, not as a default setting.
38. What’s the difference between a calculated table and a regular imported table? A calculated table is defined entirely by a DAX expression evaluated at model refresh time, generating its rows from other tables or calculations already in the model — useful for things like a disconnected date table, a summary table, or a table of distinct values derived from existing data — versus a regular table, which is loaded directly from an external data source via Power Query.
39. What’s the significance of a “disconnected table” (a table with no relationships to the rest of the model), and what’s a common use case? A disconnected table exists in the model without being joined via a relationship to any other table — commonly used to build a parameter table for what-if analysis (letting a user select a value via a slicer that a DAX measure then reads directly, without that table filtering any other data through a relationship).
40. What’s the difference between ALL, ALLEXCEPT, and ALLSELECTED? ALL removes all filters from a specified table or column entirely, ignoring whatever filter context currently exists — useful for calculating a true grand total unaffected by any slicer. ALLEXCEPT removes all filters from a table except the ones explicitly specified, useful when you want to keep just one or two filters active while clearing the rest. ALLSELECTED removes filters applied within the current visual but retains filters coming from outside it (like a slicer on the page), useful for “percentage of visible total” calculations that should respect a slicer but not the visual’s own internal grouping.
41. What is Power Query’s “query folding,” and why does it matter for performance? Query folding is when Power Query translates your applied transformation steps back into a native query (like SQL) that’s executed directly by the source system, rather than pulling all the raw data into Power BI first and then transforming it locally. It matters because folded queries push the heavy lifting to a typically more powerful source system and transfer far less data, while a broken folding chain (often caused by certain transformation steps) forces Power BI to pull everything and process it locally, which can be dramatically slower for large sources.
42. What are some common Power Query steps that break query folding, and how would you work around them? Adding a custom column with certain complex logic, changing data types in specific ways, or using certain M functions not supported by the source connector can all break folding. I’d work around it by reordering steps so folding-breaking transformations happen as late as possible in the query (after the source has already done as much filtering/aggregation as it can), or by pushing that specific logic back to the source system itself (a SQL view, for instance) if reordering alone doesn’t resolve it.
43. What’s the difference between a measure and an implicit measure (created by just dragging a numeric field into a visual)? An implicit measure is automatically created by Power BI when you drag a raw numeric column into a visual’s values area and it auto-aggregates it (typically summing it) — convenient but limited, since it can’t be reused across multiple measures or referenced by name in DAX. An explicit measure is deliberately written with DEFINE MEASURE (or created via the interface) with a name, giving you full control and reusability — generally the recommended practice for anything beyond a very quick, throwaway visual.
44. What is a role-playing dimension, and how do you handle it in Power BI? A single dimension table that logically relates to a fact table in more than one way — a Date table relating to both an Order Date and a Ship Date column in a Sales fact table, for instance. Since only one relationship between two tables can be “active” at a time, you’d typically create the additional relationship(s) as inactive and use USERELATIONSHIP inside specific measures to activate the appropriate one when needed, or create a duplicate Date table for the second role if you need both to be simultaneously active in different visuals without measure-level switching.
45. What’s the difference between Row-Level Security (RLS) and Object-Level Security (OLS) in Power BI? RLS restricts which rows of data a user can see, based on a DAX filter expression tied to their identity or role. OLS restricts access to entire tables or columns regardless of row content — used when a specific column (like salary) or entire table shouldn’t be visible to certain users at all, which RLS’s row-filtering approach doesn’t address on its own.
46. How would you implement dynamic Row-Level Security based on the logged-in user’s identity? Using the USERPRINCIPALNAME() (or USERNAME()) DAX function within an RLS role’s filter expression, typically joined against a table mapping users to the specific data they should see (like a table mapping salespeople to their assigned regions) — this way, the same RLS role definition dynamically filters differently depending on who’s actually viewing the report, rather than needing a separate static role per user.
47. What’s the difference between a snowflake schema and a star schema, and why might a modeler choose to “flatten” a snowflake into a star? A snowflake schema normalizes dimension tables further, splitting them into multiple related tables (like separating Product into Product and ProductCategory tables). Flattening into a single denormalized dimension table (a star schema) is often preferred in Power BI specifically because it reduces the number of relationships the engine has to traverse for a given query, generally improving performance and simplifying DAX, at the cost of some data redundancy within that flattened dimension table — a trade-off usually well worth it for a BI-specific model, even though it would be considered poor practice in a transactional database design.
48. What is the significance of data types and cardinality when choosing a column for a relationship key? A relationship key with lower cardinality (fewer distinct values) and a smaller, more efficient data type (an integer surrogate key rather than a long text string) generally performs better and compresses more efficiently in Power BI’s in-memory columnar engine — this is part of why surrogate integer keys are a common best practice for relationships, even when a natural text key would also technically work.
49. What’s the difference between a paginated report and a standard Power BI report? A standard Power BI report is optimized for interactive, exploratory analysis with a canvas-based, often single-screen layout. A paginated report (via Power BI Report Builder) is designed for pixel-perfect, print-ready or export-heavy documents that can span many pages, like a formatted invoice or a detailed regulatory report — chosen specifically when the output needs precise, consistent print formatting that a standard interactive report canvas isn’t built for.
50. What’s the purpose of Power BI’s “Analyze in Excel” feature? It lets users connect to a published Power BI dataset directly from Excel, using PivotTables against the live semantic model, without needing to export static data — useful for users who are more comfortable working in Excel’s familiar interface while still benefiting from the centrally maintained, single source of truth the Power BI dataset provides.
51. What’s the difference between a dataset (semantic model) and a dataflow in Power BI? A dataset (also called a semantic model) is the data model within a specific Power BI file/report, including its tables, relationships, and measures. A dataflow is a reusable, centrally maintained set of Power Query transformations stored in the Power BI Service itself, which multiple different reports/datasets can then connect to and reuse — avoiding the need to rebuild the same transformation logic separately in every report that needs the same cleaned data.
52. What’s a practical reason to use a dataflow instead of just repeating the same Power Query steps in each report? Centralizing the transformation logic in one place means a change (fixing a data quality issue, adding a new calculated column upstream) only needs to be made once in the dataflow, rather than manually replicated across every report that uses that data — reducing both duplicated effort and the risk of reports drifting out of sync with each other over time.
53. What is incremental refresh, and why would you configure it instead of a full refresh? Incremental refresh configures a dataset to only refresh recent, changed data (based on a defined date range policy) rather than reloading the entire historical dataset every time — dramatically reducing refresh time and resource usage for large datasets where most historical data doesn’t change, while still keeping recent data current.
54. What’s the difference between SUMX and simply using SUM with a calculated column that pre-computes the same value? Functionally, they can produce the same result, but a calculated column is computed once at refresh time and stored, taking up memory permanently, while SUMX computes the row-by-row calculation on the fly at query time without that storage cost — generally, if the intermediate row-by-row value isn’t independently useful elsewhere, SUMX avoids the storage overhead a calculated column would add unnecessarily.
55. What’s the significance of the VertiPaq engine’s columnar storage for Power BI’s Import mode performance? VertiPaq stores data column-by-column rather than row-by-row, with heavy compression, meaning aggregating a single column across millions of rows can be extremely fast since the engine only needs to touch that one compressed column, not entire rows — this is fundamentally why Import mode is generally so much faster than a traditional row-based query for typical BI aggregation patterns.
56. What’s a practical technique for reducing a Power BI model’s memory footprint? Removing unused columns and tables, reducing column cardinality where possible (splitting a high-cardinality datetime column into separate date and time columns, for instance, since VertiPaq compresses lower-cardinality columns more efficiently), disabling auto date/time hierarchies you don’t need, and using appropriate data types (avoiding text where a numeric or date type would work) — each individually modest, but collectively meaningful for a large model.
57. What’s the difference between a “many-to-many” relationship implemented via a bridge table versus Power BI’s native many-to-many relationship support? A bridge table explicitly models the many-to-many relationship through an intermediate table with one-to-many relationships to each side, giving explicit, predictable control over how filtering flows. Power BI’s native many-to-many relationship setting allows a direct relationship between two tables with duplicate values on both sides, which is more convenient to set up but can have subtler, less obvious filtering behavior — many experienced modelers still prefer the explicit bridge table approach for complex scenarios specifically for that predictability.
58. What’s the purpose of the SWITCH function in DAX, and how does it compare to nested IF statements? SWITCH evaluates an expression against a list of possible values and returns a corresponding result, functioning similarly to a CASE statement in SQL — generally more readable than deeply nested IF statements for the same logic, and often (though not always) more efficient for the engine to evaluate, since deeply nested IFs can become harder to both read and optimize.
59. What’s the difference between EARLIER and modern alternatives like variables (VAR) for referencing an outer row context from within a nested calculation? EARLIER is an older function for referencing a row context from an outer iteration when nested inside another row context — functional, but often confusing to read since it’s not always obvious which “earlier” context it’s referring to at a glance. Modern DAX generally favors using VAR to explicitly capture a value from the outer context into a named variable first, which is clearer, more maintainable, and avoids the ambiguity EARLIER can introduce in more deeply nested scenarios.
60. What’s the significance of aggregations (in the Power BI “Manage Aggregations” sense) for a large DirectQuery model’s performance? Aggregation tables let you define a pre-summarized, smaller Import-mode table that Power BI automatically uses instead of querying the full-detail DirectQuery source, whenever a report visual’s requested grouping matches what the aggregation table can satisfy — combining DirectQuery’s data freshness for detail-level queries with Import-mode’s speed for common summary-level queries, without needing the user to know which one is actually being used behind the scenes.
61. What’s the difference between “Auto Date/Time” and a dedicated, manually created Date table? Auto Date/Time automatically generates a hidden date hierarchy behind the scenes for every date column in the model, convenient but creating a separate hidden table per date column, which can bloat the model and doesn’t support cross-filtering between different date columns consistently. A dedicated, manually created Date table (marked as the official date table) provides a single, shared, more efficient, and more consistently behaving date dimension — the generally recommended best practice over relying on Auto Date/Time for anything beyond a very quick, throwaway analysis.
62. What’s the purpose of “Mark as Date Table” in Power BI, and what does it actually validate? It designates a specific table as the model’s official date dimension, enabling certain time-intelligence DAX functions (like TOTALYTD, SAMEPERIODLASTYEAR) to work correctly. Marking it validates that the chosen date column contains one row per calendar day with no gaps or duplicates across a contiguous range — time-intelligence functions rely on that guarantee, and they can return silently incorrect results if the underlying “date table” doesn’t actually meet it.
63. What’s a practical difference between using Power BI’s built-in time intelligence functions versus writing custom logic for a fiscal calendar that doesn’t align with the calendar year? Built-in time intelligence functions (SAMEPERIODLASTYEAR, TOTALYTD, and similar) generally assume a standard calendar year unless explicitly told otherwise via an optional year-end date parameter — for a genuinely non-standard fiscal calendar (a 4-4-5 retail calendar, for instance), you’d typically need a custom Date table with explicit fiscal period columns and custom DAX logic built around those columns, rather than relying on the built-in functions’ calendar-year assumptions to hold.
64. What’s the difference between “Live Connection” mode and Import/DirectQuery when connecting to Analysis Services or a Power BI dataset? Live Connection means the report has no local data model of its own at all — it connects directly and exclusively to an existing external semantic model (like an Analysis Services model or another Power BI dataset), inheriting its entire structure without the ability to add new relationships or DAX measures locally, distinct from DirectQuery, which still lets you build out your own additional model structure on top of a live query connection to a data source.
65. What’s the significance of the “single direction versus both directions” choice specifically for a relationship connecting a role-playing scenario, and what problem can arise? Setting a relationship to bidirectional when it’s not genuinely needed can create ambiguous or circular filter paths once the model has several interconnected tables, sometimes causing Power BI to refuse to activate a relationship at all (reporting an ambiguity error) or causing measures to behave unexpectedly by picking up filter influence from a direction you didn’t intend — generally best kept single-direction unless a specific, well-understood need (like a many-to-many bridge scenario) requires otherwise.
66. What’s a practical reason to build a dedicated “measures table” (a table with no data, existing only to organize measures) in a Power BI model? It keeps measures organized in one dedicated, easy-to-find location in the Fields pane rather than scattered across whichever table each measure happened to be created under, which becomes genuinely valuable for model maintainability as the number of measures grows — a purely organizational technique with no functional difference in how the measures actually calculate.
67. What’s the difference between “Show items with no data” and the default visual behavior in a Power BI table or matrix? By default, a visual only shows combinations of values that actually have corresponding fact data. “Show items with no data” forces the visual to also display dimension values that have no matching facts (like a product category with zero sales in the selected period) — useful for reports specifically needing to highlight gaps or absences, rather than only showing what already has activity.
68. What’s the purpose of Power BI’s “Perspectives” feature (where available), and what’s a practical use case? A perspective defines a curated, named subset of a model’s tables and fields, presented as a simplified view for specific users or purposes — useful when a large, comprehensive model has far more fields than any single audience actually needs, letting you present a focused subset (like “Sales Perspective” showing only sales-relevant fields) without duplicating the entire model or building a separate, smaller model from scratch.
69. What’s the difference between “Referenced” and “Duplicated” queries in Power Query, and when would you choose one over the other? A duplicated query creates an entirely independent copy of the original query’s steps, meaning the two are no longer connected — a change to the original doesn’t propagate. A referenced query builds on top of the original as a new starting point without copying its steps, meaning it stays connected to (and reflects changes made in) the original — generally preferred when you want a genuinely shared, single source of truth for the base transformation, with different downstream variations built from that same shared starting point.
70. What’s a practical approach to auditing which measures and columns in a large, mature Power BI model are actually being used in any report? Reviewing usage isn’t always straightforward from within Power BI Desktop alone — I’d use the external “Power BI Best Practice Analyzer” or third-party tools (like Tabular Editor combined with a model-dependency analysis, or DAX Studio’s query tracing) to identify genuinely unused fields, since an unused calculated column or measure still consumes memory and adds clutter without providing value, and periodically pruning them is a reasonable part of ongoing model maintenance.
Advanced Level
71. Explain how the VertiPaq engine’s dictionary encoding and run-length encoding (RLE) work together, and why column order/sort matters for compression. Dictionary encoding replaces each distinct value in a column with a smaller integer ID, storing the actual values only once in a lookup dictionary — beneficial for lower-cardinality columns. Run-length encoding then compresses runs of repeated identical values (or IDs) efficiently, which works best when data is sorted so identical values cluster together — this is why the loading order of your data, and even the column encoding/sort order Power BI chooses internally, can meaningfully affect a model’s final compressed size and query performance, and why a high-cardinality column with little natural repetition compresses far worse than a lower-cardinality one.
72. Explain the concept of the “Storage Engine” versus the “Formula Engine” in DAX query execution, and how understanding this split helps performance tuning. The Storage Engine (VertiPaq, or the source system in DirectQuery) handles retrieving and aggregating data efficiently, working in a highly parallelized, set-based way. The Formula Engine handles the more complex logical evaluation of DAX expressions, generally single-threaded and less efficient at raw data crunching than the Storage Engine. Performance tuning often comes down to writing DAX that pushes as much work as possible down into the Storage Engine (via functions that translate efficiently into Storage Engine queries) rather than forcing the Formula Engine to do heavy row-by-row work itself — visible directly in DAX Studio’s query plan output, which separately reports Storage Engine and Formula Engine time.
73. Explain how you’d diagnose a slow DAX measure using DAX Studio’s Server Timings feature. Server Timings breaks down a query’s execution time between Storage Engine time (data retrieval/aggregation) and Formula Engine time (complex logical evaluation), and also shows the number of Storage Engine queries generated — a measure showing a very high Formula Engine percentage, or an unexpectedly large number of separate Storage Engine queries for what looks like a simple calculation, points toward a DAX pattern that isn’t translating efficiently, worth investigating and potentially rewriting using patterns that push more work to the Storage Engine.
74. Explain what a “materialization” is in the context of DAX query plans, and why an unexpected materialization can hurt performance. Materialization refers to the Formula Engine requesting the Storage Engine build and return an intermediate, in-memory table (rather than a single aggregated value) so the Formula Engine can then process it further — necessary for some genuinely complex calculations, but an unexpectedly large or unnecessary materialization (visible in DAX Studio’s query plan as a large row count being passed between engines) can be a major hidden performance cost, often fixable by restructuring the DAX to let the Storage Engine complete more of the aggregation itself before handing off to the Formula Engine.
75. Explain the difference between “shallow” and “deep” DAX query plans, and what a deep, heavily nested plan generally implies about the underlying measure’s complexity. A shallow plan reflects a relatively direct translation of the DAX expression into a small number of efficient Storage Engine operations. A deep, heavily nested plan reflects the Formula Engine breaking the calculation into many smaller intermediate steps, often because of complex context transitions, nested iterators, or non-trivial CALCULATE filter modifications — generally correlating with real performance cost, and a very deep plan for what conceptually should be a simple calculation is a strong signal the DAX pattern used isn’t the most efficient way to express that specific logic.
76. Explain how you’d design a composite model combining a large DirectQuery fact table with several smaller Import-mode dimension tables, and the performance implications of that specific combination. This pattern (sometimes called a “DirectQuery over a large fact table with Import dimensions”) lets dimension-level filtering and slicing happen fast against the in-memory Import tables, while only the necessary aggregation against the large fact table gets pushed down as a live query to the source — I’d validate that the source system can handle the resulting query pattern efficiently (appropriate indexing on the fact table for the typical filter/join patterns the composite model will generate), since DirectQuery’s performance is now entirely dependent on the source’s own query performance for whatever the Import-side filtering narrows it down to.
77. Explain the concept of “limited relationships” in a composite model spanning Import and DirectQuery storage modes, and why they behave differently from regular relationships. A relationship crossing between an Import-mode table and a DirectQuery-mode table (or between two different DirectQuery sources) is treated as a “limited relationship,” where the engine can’t always guarantee the same referential integrity assumptions or apply certain filtering optimizations it would for a same-storage-mode relationship — this can affect both correctness in specific edge cases (like handling of blank/unmatched values) and performance, which is why composite model design deserves specific testing around these cross-storage-mode relationships rather than assuming they behave identically to a same-mode relationship.
78. Explain a scenario where implementing dynamic Row-Level Security via a many-to-many bridge table causes a measurable performance regression, and how you’d address it. If the RLS bridge table connecting users to their permitted dimension values is large or the relationship path traverses several tables before reaching the fact table, every single query now implicitly carries that RLS filter through a potentially expensive multi-hop relationship chain for every user — I’d consider whether the security model can be simplified (a more direct relationship path, or pre-computing a flattened user-to-permission mapping) or whether the bridge table itself needs better indexing/structuring (fewer unnecessary rows, appropriate column data types) to reduce that per-query overhead, since RLS filter evaluation happens on every single query a restricted user runs, so its efficiency compounds across the report’s overall responsiveness.
79. Explain how Power BI’s automatic aggregation-awareness (via Manage Aggregations) decides whether a specific visual’s query can be satisfied by an aggregation table, and a scenario where it fails to use one even though you’d expect it to. The engine checks whether the visual’s requested grouping and measures can be fully satisfied by the aggregation table’s own grain and available columns — if a visual requests a grouping at a finer grain than the aggregation table supports, or references a column not present in the aggregation table at all, the engine falls back to the detail-level DirectQuery source instead. A common surprise: a measure using a DAX pattern the aggregation-matching logic doesn’t recognize as compatible (like certain non-additive or complex conditional aggregations) may silently fail to hit the aggregation table even when the grouping itself would otherwise qualify — worth validating explicitly via DAX Studio or the Performance Analyzer rather than assuming the aggregation is being used just because it’s configured.
80. Explain the trade-offs of Power BI’s Direct Lake mode (for Fabric-connected models) compared to traditional Import and DirectQuery modes. Direct Lake reads data directly from files in a data lake (via Parquet/Delta format) without a separate explicit import/refresh step and without the live query-per-request overhead of traditional DirectQuery, aiming to combine Import-like query speed with DirectQuery-like data freshness — the trade-off is a dependency on the specific Fabric/OneLake architecture and file format requirements, and understanding when it “falls back” to a DirectQuery-like behavior (under certain memory pressure or model complexity conditions) is important for correctly setting performance expectations, since it doesn’t unconditionally guarantee Import-mode-level speed in every scenario.
81. Explain how you’d approach optimizing a Power Query M script that’s taking a very long time to refresh, beyond just checking for broken query folding. Beyond folding, I’d check for redundant or reorderable steps (combining multiple filter steps into one, applying filters as early as possible to reduce the row count subsequent steps have to process), evaluate whether certain expensive transformations (like a complex custom column calculation) could be pushed to the source system instead (a SQL view, for instance) rather than computed row-by-row in M, and check for accidental full-table scans caused by referencing a query multiple times without it being properly cached/staged, which can cause the same expensive upstream steps to be recomputed multiple times within a single refresh.
82. Explain the concept of “expanded tables” in DAX’s underlying evaluation model, and why understanding it matters for correctly predicting a measure’s behavior across relationships. Every table in a DAX evaluation is conceptually “expanded” to include columns from every table related to it (following the direction relationships allow filters to flow), meaning a calculation referencing a table implicitly also has access to — and is implicitly filtered by — related tables’ context, even without an explicit join syntax in the formula itself. Understanding this explains behavior that otherwise seems mysterious, like why a measure calculated seemingly “within” one table is still correctly filtered by a slicer on a related dimension table, since the expanded table concept is what silently carries that filter context across the relationship.
83. Explain a scenario where ALLSELECTED produces a different, and specifically more correct, result than ALL for a “percentage of total” calculation, and why. If a report has a page-level slicer (say, filtering to a specific region) and a visual showing a breakdown within that region with a “% of total” measure, using ALL would remove the region filter too, incorrectly showing each row’s percentage of the entire, unfiltered dataset rather than percentage of the currently visible, region-filtered total. ALLSELECTED specifically preserves the externally-applied region slicer while still removing the visual’s own internal row-level filtering, correctly producing “percentage of the currently selected region’s total” — the distinction matters precisely because reports commonly have exactly this kind of layered filtering (page-level slicer plus visual-level grouping) where ALL‘s blanket removal is too aggressive.
84. Explain how you’d design a Power BI model to efficiently support both a detailed, transaction-level DirectQuery reporting need and a fast, aggregated executive dashboard from the same underlying data, without maintaining two separate models. A composite model with the detail-level fact table in DirectQuery (satisfying the transaction-level reporting need with live data) paired with a pre-aggregated, Import-mode summary table at the executive dashboard’s actual required grain, configured via Manage Aggregations so the engine automatically routes executive-dashboard-level queries to the fast Import aggregation while detail-level queries still hit the live DirectQuery source — giving both use cases their appropriate performance characteristics from a single, unified model rather than maintaining and keeping two entirely separate models in sync.
85. Explain the performance and maintainability trade-offs of using Tabular Editor (an external tool) to build a model programmatically via C# scripting, versus building it entirely through the Power BI Desktop UI. Programmatic model building via Tabular Editor’s scripting enables genuinely repeatable, version-controllable, bulk operations — applying a consistent naming convention or formatting rule across hundreds of measures at once, for instance — that would be tedious or error-prone to do manually through the UI one object at a time. The trade-off is a steeper learning curve and a workflow less accessible to report authors who aren’t comfortable with scripting, which is why it’s typically the data modeler/architect’s tool for large, complex, or governed enterprise models, rather than something every report author is expected to use for day-to-day report building.
86. Explain how you’d approach diagnosing a Power BI Service scheduled refresh that intermittently fails, without a clear, consistent error message. I’d check the refresh history’s detailed error logs for each failure specifically (since “intermittent” often means the actual cause varies between occurrences, not that there’s one hidden bug), correlate failure timing against source system activity (a source database undergoing its own maintenance window, or a gateway machine under resource pressure at specific times), and consider whether the dataset’s size or complexity is pushing close to a refresh timeout limit, where an intermittent failure might reflect the refresh sometimes just barely completing and sometimes not, depending on source system responsiveness that day — genuinely intermittent issues often have a genuinely variable external cause, not a single deterministic bug to find.
87. Explain the concept of “Large Semantic Model Storage Format” and why it matters for Power BI Premium/Fabric capacity models exceeding certain size thresholds. Beyond a certain dataset size, models benefit from a storage format designed for genuinely large-scale semantic models, offering better memory management and query performance characteristics at that scale than the default format — relevant specifically for large enterprise models where the standard format’s assumptions start to become a limiting factor, and worth being aware of as a lever when working with genuinely large Premium/Fabric-hosted models rather than assuming the default configuration scales identically regardless of model size.
88. Explain how you’d approach a scenario where two different DAX measures, which should logically produce the same total when unfiltered, actually return different results — and how you’d isolate why. I’d use DAX Studio to inspect each measure’s actual query plan and, more directly, add explicit VAR statements to break each measure’s calculation into intermediate, individually-inspectable steps (temporarily, for debugging), comparing the intermediate values step by step between the two measures to find exactly where they diverge — since a discrepancy in a total that “should” match almost always traces back to a specific filter context difference (a different CALCULATE modifier, a different relationship path, an unexpected context transition) at one specific point in the calculation, which isolating step by step reveals directly rather than guessing at the whole formula.
89. Explain the security implications of Object-Level Security (OLS) not being enforced the same way across all Power BI features, and where a gap might exist. OLS enforcement historically hasn’t been uniformly guaranteed across every single Power BI surface (certain export paths, some third-party tool integrations, or specific older client versions) the same way it’s enforced within the standard Power BI Service report-viewing experience — meaning a security design relying purely on OLS to hide a sensitive column needs to be validated against every actual access path the organization’s users might use, not just the primary report-viewing scenario, since a gap in a less-common access path can otherwise silently undermine an OLS-based security design that looked complete when only tested through the main interface.
90. Explain how you’d design a Power BI governance strategy for a large organization with many self-service report authors, balancing agility against consistency and performance risk. Certified/promoted shared datasets as the single source of truth for common business metrics (reducing duplicated, potentially inconsistent measure logic across many independently-built reports), a documented modeling standard (naming conventions, star-schema expectations, a reasonable model size ceiling) enforced through review of anything requesting Premium capacity resources, and a workspace/environment structure separating genuinely self-service exploratory work from certified, broadly-shared production reports — balancing the real value of self-service agility against the real risk of dozens of independently-built, inconsistent, and potentially poorly-performing models accumulating unchecked across a large organization.
91. Explain a scenario where a seemingly correct RLS implementation still leaks data, due to a DAX measure bypassing the intended filter. If a measure uses ALL (or REMOVEFILTERS) on the specific table or column that RLS’s filter is applied to, without being deliberately designed to respect security filtering, it can inadvertently remove the RLS-imposed filter along with whatever other filter it intended to clear — since ALL doesn’t distinguish between a “normal” report filter and a security-imposed one at the DAX level by default. This is a genuinely important, non-obvious risk worth explicit testing (viewing as a restricted test user, not just as an administrator) rather than assuming RLS is airtight just because the role definition itself looks correct.
92. Explain how you’d approach performance-tuning a report page with many visuals that each individually seem fast, but the page as a whole takes a long time to fully render. I’d use Performance Analyzer to capture the actual query duration and visual rendering duration for every visual on the page simultaneously, since even individually-fast visuals can add up, and check specifically whether visuals are querying redundantly (several visuals independently requesting overlapping data that could potentially share a single underlying query pattern) or whether the page has simply accumulated more visuals than are genuinely necessary for its actual analytical purpose — sometimes the real fix for an overloaded page is consolidating or removing visuals, not purely a DAX or model-level optimization.
93. Explain the difference between “query reduction” options (like disabling cross-highlighting, or applying filters before data loads) and model-level optimization, and when each is the more appropriate lever. Query reduction options control how many separate queries a report generates and when (disabling automatic cross-highlighting between visuals, requiring an “Apply” button on slicers rather than instantly re-querying on every selection) — appropriate when the model itself is reasonably efficient but the report’s interaction pattern is generating more live queries than necessary. Model-level optimization (better DAX, better relationships, smaller model size) is appropriate when individual queries themselves are inherently slow — the two are complementary levers addressing different sources of a slow report, and diagnosing which one is actually the bottleneck (via Performance Analyzer) should come before assuming which fix applies.
94. Explain how you’d handle a scenario where a client’s Power BI model needs to support both a genuinely real-time operational view and reliable historical trend analysis, given the tension between DirectQuery’s freshness and Import’s performance. A composite model with the real-time operational data specifically scoped to DirectQuery (kept intentionally small and well-indexed at the source to keep live queries fast) paired with a separate Import-mode historical aggregation refreshed on an appropriate schedule (daily or hourly, whatever the historical trend analysis genuinely needs, which is rarely truly real-time) — being explicit with stakeholders about which specific visuals/pages are truly live versus which reflect the last scheduled refresh, since conflating the two in a single page without that distinction being clear tends to create confusion about why one number updates instantly and another doesn’t.
95. Explain a scenario where increasing a dataset’s Import-mode refresh frequency doesn’t actually solve a stakeholder’s “the data feels stale” complaint, and what the real underlying issue might be. If the stakeholder’s actual expectation is genuinely real-time or near-real-time data, no amount of increasing a scheduled Import refresh frequency (which still has a discrete interval, however short) will ever fully satisfy that expectation — the real fix in that case is reconsidering whether DirectQuery, Direct Lake, or a hybrid composite approach for the specific real-time-need portion of the data is actually required, rather than continuing to tighten an Import refresh schedule that fundamentally can’t deliver true real-time freshness no matter how frequent it becomes.
96. Explain how you’d approach a scenario where a large, complex model’s DAX measures were originally built without much structure (deeply nested formulas, no reuse), and now need to be refactored for maintainability without breaking existing reports. I’d start by identifying and extracting genuinely common sub-calculations into their own base measures (which existing complex measures can then reference, rather than duplicating the same logic inline repeatedly), using a tool like Tabular Editor to safely rename/restructure while tracking dependencies so nothing referencing the old structure silently breaks, and validate numerically that refactored measures produce identical results to their original versions across a representative set of test scenarios before considering the refactor complete — treating this as a genuine regression-testing exercise, not just a readability improvement, since a refactor that subtly changes a number’s calculation is a much worse outcome than the original messy-but-correct formula.
97. Explain the concept of “weak” versus “strong” relationships in a Power BI model (referring to the underlying key uniqueness), and why this distinction affects both correctness and performance. A “strong” relationship exists where the “one” side of the relationship has a genuinely unique key, letting the engine make certain guarantees and apply specific query optimizations. A “weak” relationship (where uniqueness on the “one” side can’t be guaranteed, more common in certain composite model or DirectQuery scenarios) forces the engine to be more conservative in how it evaluates and optimizes queries across that relationship, since it can’t rely on the same uniqueness assumptions — understanding which type of relationship you actually have helps explain otherwise-surprising performance differences between seemingly similar relationships in the same model.
98. Explain a scenario where a Power BI Premium/Fabric capacity’s memory or CPU limits genuinely constrain model design choices, beyond just “buy more capacity.” A capacity’s available memory directly limits how large an Import-mode model (or the combined memory footprint of multiple concurrently-loaded models on shared capacity) can practically be, and CPU limits affect how many concurrent complex DAX queries or refreshes can run without queuing or throttling — this can genuinely force real design trade-offs (choosing DirectQuery or a composite approach specifically to reduce memory footprint, rather than because DirectQuery is otherwise preferred, or being more disciplined about model size/complexity than you would be on an unconstrained environment) rather than the capacity sizing question being purely a cost decision separate from the actual modeling choices being made.
99. Explain how you’d design a testing/validation process for a critical financial reporting Power BI model, to catch a DAX logic error before it reaches production and produces a wrong number executives rely on. I’d build a parallel validation dataset or query (ideally derived independently, like a direct SQL query against the source computing the same headline metrics) to cross-check the DAX model’s key measures against, specifically for the metrics stakeholders would actually notice and act on if wrong, treat any discrepancy as a blocking issue requiring root-cause investigation before release, and maintain that validation check as an ongoing, repeatable process (not a one-time check at initial build) so a later, seemingly unrelated model change doesn’t silently reintroduce a discrepancy that a one-time validation would have missed.
100. Walk through a genuinely difficult Power BI performance or data modeling problem you’ve solved or would expect to solve, demonstrating your diagnostic process end to end. Intentionally open-ended, since interviewers are evaluating process and depth over a memorized answer. A strong structure to demonstrate regardless of the specific problem: (1) use Performance Analyzer and/or DAX Studio to get objective, measured evidence of where time is actually being spent — Storage Engine versus Formula Engine, specific slow visuals — rather than guessing based on which measure “looks complicated,” (2) form a specific hypothesis grounded in that evidence, understanding the underlying mechanism (filter context, query folding, relationship cardinality) rather than pattern-matching to a remembered fix, (3) validate the hypothesis with a targeted, isolated test before committing to a broader model change, (4) apply the narrowest fix addressing the confirmed cause, and (5) re-measure afterward with the same tools to confirm the fix genuinely worked, not just that the report subjectively feels faster. Interviewers consistently favor candidates who can point to a specific DAX Studio timing breakdown or a specific filter context explanation over candidates who can only describe Power BI performance issues in vague, generic terms.
A Closing Thought
The idea running through nearly every advanced answer here: Power BI’s behavior is never arbitrary — filter context, the VertiPaq engine’s compression model, and the Storage/Formula Engine split are all specific, learnable mechanisms, and the real skill is tracing a confusing number or a slow report back to the specific mechanism actually responsible, rather than reaching for a generic “add a measure” or “just import less data” fix.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
