Power BI Interview Questions

Note: The strongest Power BI answers are about decisions, not visuals. A dashboard nobody acts on is a failed project regardless of how it looks.

Cover:

• The audience and the question they needed answered. An executive wanting one number weekly and an analyst wanting to slice freely need completely different reports.
• The data sources and their state. Where the data came from, and how bad it was. Real projects spend most of their time here.
• The model. Whether you built a star schema, how many fact and dimension tables, and the key measures. This is what separates someone who builds models from someone who drags fields onto a canvas.
• What changed as a result. A decision made, a process altered, a manual report retired, or hours saved.

If adoption was poor, say so and explain why — usually trust in the numbers, or answering a question nobody was asking.

The main risk in BI work is building something technically correct that nobody uses, so show that you interrogate the request.

• Start with the decision, not the fields. Ask what action they would take differently depending on what the report shows. If there is no answer, the report has no purpose and you should say so early.
• Identify the audience precisely. An executive summary, an operational monitor, and an analytical exploration tool are three different products. Trying to serve all three in one report produces something that serves none.
• Agree definitions in writing. "Active customer" and "revenue" mean different things to different departments, and a disagreement discovered after launch destroys trust in the whole report.
• Establish granularity and refresh frequency. Real-time is expensive and usually unnecessary — ask what decision requires it.
• Check the data exists before promising anything. This is where most requirements gathering fails.

Note: Mentioning that you build a rough mock-up early and iterate is a strong point. People cannot specify a dashboard in the abstract, but they can react to one immediately.

This happens to everyone in BI, and how you handle it is the whole question.

• Check your own work first, quickly and without defensiveness. Trace the number back through the measure, the model relationships, and the source. A surprising number of disputes turn out to be a filter context issue or a many-to-many relationship producing inflated totals.
• Find where the two numbers diverge. If someone's spreadsheet disagrees with your report, reconcile at a granular level rather than arguing about the total. The gap is almost always a definition difference — a different date field, an excluded category, or a currency conversion.
• Be willing to be wrong publicly. If your report was incorrect, say so immediately and fix it. Trying to defend a wrong number costs far more credibility than the error did.
• If the report is right and the news is bad, present the evidence calmly and let people react. Do not soften a genuine finding.

Note: The systemic fix is worth adding — documented measure definitions and a data dictionary, so "what does this mean" is answered once rather than every time.

Adoption is the real deliverable, and it is mostly about trust and fit rather than features.

Building trust:

• Show the data freshness on the report. A visible "last refreshed" timestamp prevents the most common source of confusion.
• Reconcile against a source people already believe. If the finance number matches finance's own report, the rest of the dashboard inherits that credibility.
• Document measure definitions where users can see them, so nobody has to guess what "active user" counts.
• Fix errors fast and visibly. A report that was wrong once and corrected quickly retains trust; one with a known unfixed error is dead.

Building usage:

• Make it fast. A report taking twenty seconds to load will not be opened twice.
• Deliver it where people already are — a Teams tab, a subscribed email, or an app rather than a link they must remember.
• Train briefly and watch someone use it. Observing a user struggle for two minutes teaches you more than a feedback form.
• Check usage metrics and retire what nobody opens.

How you keep up: Power BI ships monthly, so the release blog is the primary source. Beyond that, the community — the Power BI community forums, SQLBI for DAX and modelling depth, and Guy in a Cube for practical walkthroughs. Most usefully, rebuilding something you already made using a new capability, because reading about a feature rarely tells you where it breaks.

How you filter:

• Modelling and DAX fundamentals repay study far more than new visuals. Filter context, relationships, and star schema design are what determine whether a report is correct and fast — and they change slowly.
• Preview features are not for production. They can change or disappear, and reports built on them can break.
• Does it solve a problem you have? A new visual type is irrelevant if your reports are slow because the model is wrong.

Note: Being able to say you deepened your understanding of a fundamental — filter context, or the difference between calculated columns and measures — rather than only collecting features, is the more impressive answer. Most Power BI problems in the wild are modelling problems, not feature gaps.

The interviewer is testing whether you diagnose methodically or just try random tips. Use STAR, and make sure you measured before and after.

• Situation: for example, a sales dashboard used by 60 regional managers took 25 to 30 seconds per slicer click, and managers had quietly gone back to exporting data to Excel.
• Task: bring every interaction under about three seconds without removing anything the business relied on.
• Action: walk through a sequence, not a list of guesses:
  • Recorded a baseline with Performance Analyzer and identified the three slowest visuals.
  • Copied their DAX queries into DAX Studio and saw most time was spent in the formula engine.
  • Found the root causes: a 40-column flat table, a high-cardinality datetime column, one bidirectional relationship and a measure using FILTER(Sales, ...) over the whole fact table.
  • Rebuilt the model as a star schema, split datetime into separate date and time columns, removed unused columns in Power Query, and rewrote the measure with a simple column filter.
  • Moved detail tables to a drillthrough page, cutting the landing page from 22 visuals to 9.
• Result: quantify it. Model size fell from 1.2 GB to 350 MB, page load dropped from 28 seconds to under 3, and the usage metrics report showed weekly active users rising again.

Close with what you now do by default, such as checking cardinality and visual count before publishing any report.

Note: Avoid answers like "we moved to Premium capacity". Buying more hardware is sometimes right, but interviewers want to hear that you fixed the model and the DAX first.

Migration questions test planning and change management as much as technical skill. Structure your answer in phases.

• Inventory and rationalise: for example, you found 45 Excel reports across finance and sales, many of them near-copies with slightly different logic. Mapping who used each one and which decisions it supported let you consolidate them into 8 Power BI reports on a single shared semantic model.
• Agree definitions before building: lock down revenue, margin and customer counts with the report owners so the new version does not inherit old inconsistencies.
• Automate the plumbing: replace manual copy-paste with Power Query connections to the source systems and a scheduled refresh through the gateway.
• Reconcile in parallel: run both versions side by side for one month-end close and document every difference, whether it was a Power BI error or a long-standing spreadsheet error.
• Roll out in waves: start with one friendly team, run short hands-on sessions, publish through an app, and keep Analyze in Excel for power users who still want pivots.
• Retire the old files: agree a cut-off date with the sponsor, otherwise both versions live forever.

Result: give numbers, such as 30 hours of manual preparation removed each month and month-end reporting delivered two days earlier.

Note: Mentioning that the parallel run exposed errors in the old spreadsheets is powerful. It shows the migration improved accuracy, not just the look of the reports.

This question checks judgement as well as security features. Show that you involved data owners and applied least privilege, then describe the controls.

• Situation: for example, an HR attrition dashboard containing salary bands and performance ratings, needed by HR business partners, line managers and the leadership team.
• Decide the rules with owners: you did not guess. You sat with HR and legal to agree that managers see only their own teams, only HR sees individual salaries, and leadership sees aggregates.
• Row-level security: built a security table mapping manager email to department and used USERPRINCIPALNAME() in a dynamic role, so one role served every manager.
• Object-level security: hid the salary columns from non-HR roles entirely, rather than just hiding a visual.
• Workspace hygiene: consumers received the report through an app with Viewer access, because RLS does not apply to workspace Admins, Members or Contributors.
• Other controls: applied a sensitivity label, restricted export of underlying data, and used Entra ID security groups instead of named individuals.
• Testing and sign-off: used View as role with real manager accounts and had HR sign off before go-live.

Result: the report launched without a single access incident, and a quarterly access review was set up with HR.

Note: In India, referring to the Digital Personal Data Protection Act, 2023 and the principle of collecting and exposing only what is necessary shows maturity beyond the tool.

Conflicting definitions are common, and the interviewer wants to see that you facilitated a decision rather than silently picking one side.

• Situation: for example, Sales counted an active customer as anyone who ordered in the last 12 months, while Marketing counted anyone who logged in during the last 90 days. The two dashboards differed by 40 percent and leadership lost confidence in both.
• Make the difference visible: you built a small reconciliation showing both numbers from the same data, with the exact logic behind each one. This turned an argument about whose data was wrong into a discussion about what each team actually meant.
• Bring in the right people: you arranged a short session with both KPI owners and a business sponsor who could make the final call.
• Agree and document: the outcome might be one company-wide definition, or two legitimate metrics with distinct names such as Active Customers (Purchase, 12M) and Engaged Users (Login, 90D).
• Implement once: the measures lived in a single certified semantic model, with descriptions visible on hover and a glossary page in the report.

Result: both teams used the same model, and the leadership pack showed one number with a clear label.

Note: The key message is ownership. Analysts implement definitions, but business owners decide them. Saying that clearly shows you understand where your role ends.

Every Power BI developer has faced this, so a confident, honest answer with clear prevention steps scores well. Split it into response and prevention.

• Situation: for example, the Monday 7 am refresh of the leadership sales dashboard failed, and the weekly review was at 10 am.
• Triage fast: you checked refresh history for the error message. Typical causes are an offline gateway, expired credentials, or a schema change at source. Here, the source team had renamed a column, breaking a Power Query step.
• Communicate early: before fixing anything, you told stakeholders what had happened, that the report still showed Friday's data, and when to expect an update. A "data as of" card on the report made the staleness obvious.
• Fix and verify: updated the step, republished, ran a manual refresh and spot-checked key totals against the source before confirming it was resolved.
• Prevent a repeat:
  • Refresh failure alerts sent to a team mailbox, not one person.
  • A service account instead of personal credentials on the data source.
  • A two-machine gateway cluster for high availability.
  • An agreement that the source team announces schema changes in advance.

Result: the report was live by 9:30 and there were no repeat failures of that type in the following six months.

Note: Do not blame the source team in your answer. Focus on what you controlled, and show that the incident produced a lasting improvement.

They are three distinct layers, and confusing them is the most common source of Power BI trouble.

• Power Query (M language) — the extract and transform layer. It connects to sources and shapes data before it enters the model: removing columns, filtering rows, unpivoting, merging queries, changing types. Everything here happens at refresh time.
• The data model — how tables relate to each other. Relationships, cardinality, filter direction, hierarchies, and formatting. This is where report correctness is largely determined.
• DAX (Data Analysis Expressions) — the calculation layer. Measures and calculated columns evaluated when the report is used, responding to whatever filters the user has applied.

The rule that follows: do transformation as far upstream as possible. Best of all in the source database, otherwise in Power Query, and only in DAX when the calculation genuinely depends on user interaction. Filtering rows or renaming columns in DAX rather than Power Query is a common mistake that costs both performance and clarity.

Note: The corollary about calculated columns versus measures follows the same logic — a calculated column is computed at refresh and stored in memory, so if the value does not need to respond to filters, it should have been created in Power Query instead.

A star schema separates tables into two kinds:

• Fact tables — the events or transactions you measure. Long and narrow, containing numeric values and foreign keys. Sales, orders, page views.
• Dimension tables — the context you slice by. Short and wide, containing descriptive attributes. Date, Product, Customer, Region.

Dimensions relate to facts one-to-many, with filters flowing from dimension to fact — producing a diagram shaped like a star.

Why it matters so much in Power BI:

• The engine is built for it. VertiPaq compresses columns, and a star schema produces exactly the pattern it optimises for. A flat wide table or a snowflake with many joins performs worse, often dramatically so.
• Filters behave predictably. Ambiguous paths and bidirectional relationships in a poorly shaped model cause wrong totals that are extremely hard to debug.
• DAX becomes simpler. Many complex measures written to work around a bad model become one line against a good one.
• Slicers work naturally, because dimension attributes are where they belong.

Note: The single most important practical instance is a dedicated Date dimension marked as a date table. Without one, time intelligence functions such as SAMEPERIODLASTYEAR and YTD either fail or return wrong results — and this catches out a large proportion of candidates.

This is the most common Power BI interview question, and the answer is about when the calculation happens.

A calculated column:

• Is computed at refresh time, row by row, and the result is stored in the model, consuming memory.
• Evaluates in row context — it can see the other columns of its own row.
• The value is fixed and does not change as the user filters.
• Use it when you need a value to slice, filter, or group by — a category derived from a numeric range, or a concatenated key.

A measure:

• Is computed when the report is viewed, and nothing is stored.
• Evaluates in filter context — it responds to whatever slicers, rows, and columns are in play, which is why the same measure shows a different number in every cell of a matrix.
• Must aggregate; it cannot return a value for a single row without one.
• Use it for anything you want to see aggregated — totals, averages, ratios, year-over-year change.

The guidance: prefer measures. They cost no memory, respond correctly to user interaction, and remain correct at every level of aggregation.

Note: The classic error is calculating a ratio as a calculated column and then summing it. Summing percentages gives nonsense — a measure dividing the summed numerator by the summed denominator is the correct approach, and interviewers ask this deliberately.

Row context means "the current row". It exists in calculated columns and inside iterator functions such as SUMX and FILTER. It lets you reference other columns of the same row.

Filter context is the set of filters applied when a value is evaluated — from slicers, from the rows and columns of a visual, from page filters, and from relationships. A measure returns a different number in every cell because each cell has a different filter context.

CALCULATE is the most important function in DAX because it is the only one that modifies filter context.

Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

It evaluates the first argument under filter conditions you specify. It can add filters, remove them with ALL or REMOVEFILTERS, or replace them.

The classic pattern is percentage of total, which needs the denominator to ignore the current row's filter:

% of Total =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)))

Note: CALCULATE also performs context transition — inside a row context it converts the current row into an equivalent filter context. This is why wrapping a measure in CALCULATE inside SUMX behaves differently from calling it directly, and it is the single concept that most often trips people up in DAX interviews.

Three storage modes with genuinely different trade-offs.

• Import — data is loaded into Power BI's in-memory VertiPaq engine and compressed. Fastest by a wide margin, supports the full range of DAX and Power Query, and works offline. The limits are that data is only as fresh as the last refresh, model size is capped by capacity, and refresh time grows with volume. This should be the default.
• DirectQuery — no data is stored; every visual generates a query against the source at view time. Use it when data must be near real-time, or the dataset is too large to import, or governance forbids copying data. The costs are significant: report performance depends entirely on the source database, many DAX functions are unavailable or slow, Power Query transformations are restricted, and every interaction generates load on the source.
• Live Connection — connects to an existing model in Analysis Services or a Power BI dataset. You build visuals but do not own the model, which is exactly the point: one governed model serves many reports and definitions stay consistent.

Composite models let you mix them — importing dimensions while leaving a huge fact table in DirectQuery, with aggregation tables to answer most queries from memory.

Note: The interview point is that DirectQuery is often chosen for the wrong reason. "Real-time" usually means "refreshed hourly", which Import handles perfectly at a fraction of the complexity.

Relationships connect tables so filtering one filters the other. Each has a cardinality and a cross-filter direction.

Cardinality:

• One-to-many — the standard and desirable case, from a dimension to a fact.
• Many-to-one — the same thing described from the other side.
• One-to-one — usually a sign two tables should be merged.
• Many-to-many — supported, but it creates ambiguity and is often a modelling smell. The cleaner solution is usually a bridge table.

Cross-filter direction is single by default: the one side filters the many side. That is normally what you want — Product filters Sales, not the reverse.

Why bidirectional relationships are dangerous:

• Ambiguity. With several tables and bidirectional filters, more than one filter path can exist between two tables, and the engine either refuses the model or picks a path you did not intend.
• Wrong totals that look plausible — the worst kind of error, because nobody notices.
• Performance cost, since the engine must propagate filters both ways.

The better approach: keep relationships single-direction and use CROSSFILTER inside a specific measure when you genuinely need bidirectional behaviour. That confines the effect to one calculation instead of the whole model.

Note: Only one relationship between two tables can be active. Use USERELATIONSHIP inside CALCULATE to activate an alternative — the standard pattern for a fact table with both an order date and a ship date.

Diagnose first with Performance Analyzer in Power BI Desktop, which shows the time each visual spends on DAX query, visual display, and other. That immediately tells you whether the problem is the model, the DAX, or the report layout. DAX Studio and VertiPaq Analyzer go further, showing which columns consume the most memory.

Model-level fixes — usually the biggest wins:

• Remove columns you do not use. Every column costs memory and refresh time. High-cardinality columns such as GUIDs and timestamps to the second are the most expensive; splitting a datetime into date and time columns often shrinks a model dramatically.
• Adopt a star schema. Flat wide tables and snowflakes both cost performance.
• Reduce cardinality by rounding numbers to the precision you actually report on.
• Filter rows at the source rather than importing history nobody looks at.

DAX-level fixes:

• Avoid iterators over large tables where a simple aggregation would do.
• Use DIVIDE rather than / with an IF guard.
• Use variables with VAR to compute something once instead of repeatedly.
• Avoid FILTER over an entire table inside CALCULATE when a simple boolean filter argument works.

Report-level fixes: fewer visuals per page — each one issues its own query — limit slicers, and avoid heavy custom visuals.

Row-level security (RLS) restricts which rows a user can see within a single dataset, so one report serves everyone while each person sees only their own data.

Static RLS defines a role with a fixed DAX filter:

[Region] = "South"

You then assign users to that role. Simple, but it does not scale — a role per region quickly becomes unmanageable.

Dynamic RLS is the approach used in practice. One role filters based on who is viewing:

[SalesRepEmail] = USERPRINCIPALNAME()

For a hierarchy, add a permissions table mapping users to the regions or accounts they may see, relate it to the model, and filter through that relationship. Adding a new user then means adding a row to a table, not editing the model.

Important details:

• Test with "View as role" in Desktop before publishing, and test again in the Service — behaviour can differ.
• Filters propagate through relationships, so a filter on a dimension flows to the fact table. Check that it reaches everything it should, and watch for bidirectional relationships bypassing it.
• Workspace admins and members bypass RLS. Users must have Viewer access and be assigned to a role for it to apply — a genuine and frequently overlooked security gap.
• RLS filters rows, not columns or measures. Object-level security handles hiding columns.

• Power BI Desktop — the free Windows authoring tool. This is where you connect to data, transform it in Power Query, build the model, write DAX, and design the report. All real development happens here.
• Power BI Service — the cloud platform at app.powerbi.com. You publish to it, and it handles scheduled refresh, sharing, dashboards, subscriptions, alerts, and governance. Reports can be edited here, but the modelling experience is limited.

Within the Service:

• Workspace — a container for datasets, reports, dashboards, and dataflows, with role-based access: Admin, Member, Contributor, and Viewer. This is the development and collaboration space, and it is where your team works.
• App — a curated, published package from a workspace, distributed to consumers. This is the consumption layer. Users get a clean, read-only experience with only the content you chose to include, and you can update the workspace without affecting them until you republish.

The distinction that matters: give consumers an app, not workspace access. Workspace access exposes work in progress and makes permissions harder to manage.

Note: A dashboard and a report are also different. A report is multi-page and interactive, built from one dataset; a dashboard is a single-page collection of pinned tiles that can come from several reports. Mixing up these terms is a common giveaway.

Scheduled refresh re-runs the queries in a published dataset on a timetable. On shared capacity you get up to eight refreshes a day; on Premium, up to forty-eight.

The on-premises data gateway is required for any source that is not cloud-accessible — an on-premises SQL Server, a file share, or a database behind a firewall. It should be installed in standard mode on a server rather than personal mode on someone's laptop, and clustered for redundancy.

Incremental refresh solves the problem of a growing fact table. Instead of reloading everything, you partition the table by date and refresh only the recent partitions.

You configure it with two parameters, RangeStart and RangeEnd, used to filter in Power Query, then set a policy such as "store 5 years of data, refresh the last 10 days".

Why it matters: a refresh that took two hours reloading five years of history can drop to two minutes. It also reduces load on the source and makes datasets larger than would otherwise be refreshable practical.

Note: Two practical points. Query folding is essential — the date filter must be pushed down to the source as a WHERE clause, and if the transformation breaks folding, incremental refresh gives no benefit. And handle late-arriving data deliberately: if records can be backdated beyond your refresh window, they will be missed, so either widen the window or trigger a full refresh periodically.

Power BI supports three cardinalities:

• One-to-many (or many-to-one) — the normal star schema case. The key is unique on the dimension side, for example one row per product in Product and many rows per product in Sales.
• One-to-one — the key is unique on both sides. It usually means the two tables should be merged in Power Query, since keeping them separate adds complexity for no benefit.
• Many-to-many — the key is not unique on either side. It is legitimate in some cases but needs care.

Two ways to handle a genuine many-to-many:

• Bridge table. The classic pattern for, say, customers and jointly-held bank accounts. Create Customer, Account and a CustomerAccount bridge, each linked one-to-many. Filtering from Customer to the fact table has to pass through the bridge, so either set the bridge-to-Account relationship to filter both ways, or enable it only inside a measure:

  Balance by Customer =
  CALCULATE (
      [Total Balance],
      CROSSFILTER ( CustomerAccount[AccountID], Account[AccountID], BOTH )
  )

• Native many-to-many cardinality. Useful when tables are at different grain, such as a Budget table at Category and Month level related to the Product dimension on Category. The relationship works without an artificial key table.

What to expect: totals may not equal the sum of the rows, because a joint account counts once per customer but only once in the grand total. That is correct behaviour, but users should be told about it.

Note: Many-to-many relationships are weak relationships, so they are slower and do not create blank rows for missing keys. Use them deliberately, never just to make a relationship error go away.

Only one active relationship can exist between two tables. Any other relationship between the same pair is created as inactive, shown as a dashed line in the model view. It does nothing unless a measure explicitly switches it on.

The classic case is a role-playing Date dimension. A Sales table has OrderDate, ShipDate and DueDate, and all three should relate to the Date table. You make OrderDate the active relationship and leave the other two inactive. Then you activate them per measure:

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)

Inside that CALCULATE the ShipDate relationship becomes active and the OrderDate one is ignored, so a matrix by month shows sales by the month they shipped.

Rules and limitations:

• The relationship must already exist in the model. USERELATIONSHIP cannot create one.
• It only works as a CALCULATE or CALCULATETABLE modifier.
• It cannot be used when row-level security is defined on the table the measure relies on, for example a customer relationship where the Customer table has an RLS rule.

The alternative is to load the Date table twice, as Order Date and Ship Date, each with an active relationship. That costs extra memory but lets users slice by both dates at the same time with any measure, without writing a special version of each one.

Note: A good interview answer mentions the trade-off: USERELATIONSHIP keeps the model small but multiplies measures, while duplicated role-playing dimensions multiply tables. Pick based on how often users need each date.

A proper Date table needs:

• One row per day with no gaps, covering complete years across all dates in the model.
• A unique Date column of Date data type.
• The table marked as a date table (Table tools, Mark as date table) so time intelligence behaves correctly.
• A one-to-many relationship from Date[Date] to each fact table's date column.

Ideally it comes from the data warehouse or Power Query, but DAX is common and fine:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 4, 1 ), DATE ( 2027, 3, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month No", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Fiscal Year", "FY" & IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ) + 1, YEAR ( [Date] ) )
)

The fiscal year column follows the Indian April to March year, so April 2025 falls in FY2026. Set Sort by column on Month using Month No, or months sort alphabetically. For fiscal year-to-date, pass the year-end date: TOTALYTD([Sales], 'Date'[Date], "3/31").

Why turn off Auto date/time (Options, Data Load):

• It creates a hidden date table for every date column, bloating the model, sometimes massively.
• Those hidden tables are not shared, so you cannot slice two fact tables by one common calendar.
• They cannot hold fiscal years, holidays or working-day flags.

Note: Turn it off in both the Global and Current File settings. The global option only affects new files, which is why existing reports often still carry the hidden tables.

Time intelligence functions take the dates currently in filter context and return a shifted or extended set of dates. That table is then used as a filter inside CALCULATE. Because they work on dates rather than on month or year labels, they need a complete, marked Date table.

• DATESYTD / TOTALYTD — every date from the start of the year to the last date in context.
• SAMEPERIODLASTYEAR — the same dates shifted back one year. It is equivalent to DATEADD(..., -1, YEAR).
• DATEADD — shifts by any number of days, months, quarters or years, for example the previous month.
• PARALLELPERIOD — returns the whole shifted period, such as the entire previous quarter, regardless of how many dates are selected.

Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )

Sales PY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Sales PM =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )

YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorSales = [Sales PY]
RETURN
    IF ( NOT ISBLANK ( PriorSales ), DIVIDE ( CurrentSales - PriorSales, PriorSales ) )

The "3/31" argument moves the year end to March for an Indian financial year. DIVIDE handles a zero prior year safely, and the IF avoids showing growth for periods with no history.

A common trap: the current month is incomplete, so comparing month-to-date sales against a full prior month exaggerates the decline. Fix it by limiting the current period to dates up to the last date that has sales, or by adding a flag column for dates up to today.

Note: Interviewers often ask why SAMEPERIODLASTYEAR returns blank. The usual answers are an unmarked or incomplete Date table, or a relationship built on a datetime column that includes a time part.

SUM aggregates one column as it stands. Iterators (the X functions) loop through a table row by row, evaluate an expression in each row's row context, and then aggregate the results.

When you need one:

• The value does not exist as a column. Revenue is quantity times price, so instead of adding a calculated column, calculate it on the fly:

  Revenue = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

  Summing a pre-multiplied calculated column gives the same answer but stores an extra column in memory.
• You need an aggregate at a specific grain. Average monthly sales is not the average transaction value:

  Avg Monthly Sales = AVERAGEX ( VALUES ( 'Date'[Month Year] ), [Total Sales] )

  Here the iterator loops over months, and each month's total is calculated through context transition.
• Other iterators: MINX and MAXX (best or worst month), COUNTX, RANKX for ranking, and CONCATENATEX to build a text list of selected items.

Performance guidance:

• SUM(Sales[Amount]) is simply shorthand for SUMX(Sales, Sales[Amount]), so iterators are not slow by nature.
• Simple arithmetic across columns of the same table is pushed to the storage engine and is fast even over millions of rows.
• Iterating a large fact table and calling a measure in each row is expensive, because every row triggers context transition. Iterate the smallest table that gives the right grain, such as VALUES of a dimension column.

Note: A strong candidate adds that nested iterators, meaning an X function inside another X function over large tables, are the usual culprit when a single measure suddenly takes seconds to calculate.

Context transition is what happens when CALCULATE runs inside a row context: the current row is converted into an equivalent filter context, with every column of that row becoming a filter.

It happens explicitly when you write CALCULATE inside an iterator or calculated column, and implicitly whenever you reference a measure, because every measure is wrapped in an invisible CALCULATE.

Example. In a calculated column on the Customer table:

Total Sales Col 1 = SUM ( Sales[Amount] )          -- grand total on every row
Total Sales Col 2 = CALCULATE ( SUM ( Sales[Amount] ) ) -- each customer's own sales
Total Sales Col 3 = [Total Sales]                    -- same as Col 2, implicit CALCULATE

A row context alone does not filter anything, so the first column ignores the relationship. Context transition turns "this customer's row" into a filter that flows to Sales.

Why it surprises people:

• Duplicate rows. Transition filters on all columns of the row. If a fact table has two identical rows, each one filters to both, so an expression like SUMX(Sales, [Total Sales]) double counts.
• Performance. Calling a measure while iterating a fact table of 10 million rows triggers 10 million transitions. Iterate a dimension or VALUES(Sales[CustomerID]) instead.
• Measures versus inline expressions. Replacing [Total Sales] with its formula text inside an iterator changes the result, because the inline version has no implicit CALCULATE.

Note: Context transition is one of the most frequent senior-level DAX questions. Being able to explain the calculated-column example above in one sentence is often enough to show you genuinely understand it.

All four remove filters, but they differ in which filters they remove and in where they can be used.

• ALL(table or columns) — removes every filter from the given table or columns. It also works as a table function that returns all rows regardless of filters, so it can be iterated.
• REMOVEFILTERS(table or columns) — does the same job as ALL but only as a CALCULATE modifier. It exists purely to make intent clearer.
• ALLEXCEPT(table, columns) — removes filters from every column of that table except the listed columns.
• ALLSELECTED(table or columns) — removes filters created inside the visual, such as matrix rows, but keeps filters from outside it, such as slicers and page filters. It returns what the user has selected.

Three percentage measures show the difference:

% of Grand Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) ) )

% of Category =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[Category] ) ) )

% of Visible Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Product ) ) )

If a slicer limits the report to three categories, the first measure still divides by all products, while the third divides by the three selected categories, so its rows add to 100 percent.

Points worth mentioning:

• ALLEXCEPT only affects the table you pass. Filters on other tables, such as Date or Region, remain.
• ALLSELECTED depends on the query shape and is the hardest to predict inside complex measures, so keep it for visual totals.

Note: Prefer REMOVEFILTERS over ALL when you only mean remove the filter. Other developers can then tell at a glance that you are not using ALL as a table.

A Boolean filter is shorthand. These two measures are identical:

Red Sales = CALCULATE ( [Total Sales], Product[Colour] = "Red" )

Red Sales = CALCULATE ( [Total Sales], FILTER ( ALL ( Product[Colour] ), Product[Colour] = "Red" ) )

Because of the hidden ALL, a Boolean filter replaces any existing filter on that column. In a matrix with Colour on rows, every row, including Blue and Green, shows Red sales.

FILTER over a whole table, such as FILTER(Product, Product[Colour] = "Red"), behaves differently. It iterates only the rows already visible, so it intersects with existing filters, and it applies the entire filtered table as the filter. That is usually slower and can carry unintended filters on other columns.

When you genuinely need FILTER: when the condition uses a measure, because Boolean filters cannot contain measures:

Sales from Big Customers =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Customer[CustomerID] ), [Total Sales] > 100000 )
)

Note that the FILTER iterates just one column, not the whole Customer table. Boolean filters can reference several columns only if they come from the same table.

KEEPFILTERS changes the Boolean behaviour from replace to intersect:

Red Sales Kept = CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Colour] = "Red" ) )

Now the Red row shows Red sales and every other colour row shows blank, which is often what users actually expect.

Note: A simple rule to quote in interviews: filter columns, not tables. Use Boolean filters for column conditions, FILTER over a single column when a measure is involved, and KEEPFILTERS when the result must respect what is already selected.

A variable is declared with VAR and the result is returned with RETURN. The key rule is that a variable is evaluated once, in the context where it is defined, and is then a constant.

Margin Status =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
VAR MarginPct = DIVIDE ( Revenue - Cost, Revenue )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( Revenue ), BLANK (),
        MarginPct >= 0.3, "Healthy",
        MarginPct >= 0.1, "Watch",
        "At risk"
    )

Benefits:

• Readability. Each step has a name, so a reviewer can follow the logic without untangling nested brackets.
• Performance. Without variables, MarginPct would be recalculated in every SWITCH branch. With them, each value is calculated once.
• Debugging. Temporarily change RETURN to return an intermediate variable to check it in a visual.
• Capturing outer context. In iterators, a variable can hold a value from before the iteration starts, which replaces the old EARLIER function.

The trap interviewers love:

Wrong Total =
VAR SalesNow = [Total Sales]
RETURN
    CALCULATE ( SalesNow, REMOVEFILTERS ( 'Date' ) )

This returns the current sales, not the all-time total. SalesNow was already calculated before CALCULATE changed the filters, and CALCULATE cannot re-evaluate a constant. The measure reference must sit inside CALCULATE instead.

Variables can also hold tables, for example VAR TopCustomers = TOPN(10, VALUES(Customer[Name]), [Total Sales]), which can then be passed to CALCULATE as a filter.

Note: Using variables by default is widely treated as a sign of a mature DAX developer. If a measure references the same sub-expression twice, it should almost certainly be a variable.

A running total sums everything from the start of time, or the start of a period, up to the last date visible in the current cell.

Running Total Sales =
VAR LastVisibleDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= LastVisibleDate
    )

How it works: the variable captures the last date in the current month or day. REMOVEFILTERS clears every filter on the Date table, including month and year columns, and the Boolean filter then keeps all dates up to that point.

Variations:

• Resetting each year: TOTALYTD([Total Sales], 'Date'[Date], "3/31") or CALCULATE([Total Sales], DATESYTD('Date'[Date], "3/31")) for an April to March year.
• Running total over a non-date order, such as a Pareto chart of products by sales, needs a rank. The WINDOW function or a RANKX-based filter handles that.
• Visual calculations in recent Power BI versions offer RUNNINGSUM([Total Sales]) directly on a visual, which is convenient when the running total is only needed in one place.

Pitfalls:

• Forgetting to clear other Date columns. If you filter only Date[Date] without REMOVEFILTERS and the table is not marked as a date table, the month filter on rows survives and the total resets every month.
• Future dates. The running total keeps flat-lining into months with no sales. Wrap it in IF(NOT ISBLANK([Total Sales]), ...) or restrict it to dates up to the last sale.
• Using the fact table date instead of the Date dimension, which ignores months with no transactions.

Note: Writing this measure live is one of the most common Power BI whiteboard tasks. Explaining why the variable is needed, because MAX must be evaluated before the filters change, earns more credit than the formula itself.

RANKX iterates a table, evaluates an expression for each row, and returns the position of the current value within that list.

Product Rank =
IF (
    ISINSCOPE ( Product[Product Name] ),
    RANKX ( ALL ( Product[Product Name] ), [Total Sales], , DESC, DENSE )
)

Why every row shows 1: if you write RANKX(VALUES(Product[Product Name]), [Total Sales]), each row of the visual is filtered to a single product, so VALUES returns a one-row table and that product is ranked against itself. The table argument must remove the row filter, which is why ALL is used.

Choices to make:

• ALL or ALLSELECTED. ALL ranks against every product in the model. ALLSELECTED ranks only among products that survive the slicers, which is usually what users expect on a filtered page.
• Ties. SKIP, the default, gives 1, 2, 2, 4. DENSE gives 1, 2, 2, 3.
• Totals. Without the ISINSCOPE check, the total row shows a meaningless rank of 1.
• Blanks. Products with no sales still get a rank at the bottom. Wrap it in a check if they should be excluded.

Related functions:

• RANK, a newer window function, can break ties using several ORDERBY columns, which RANKX cannot do cleanly.
• TOPN returns the top N rows as a table, useful for measures like sales of the top 10 customers.
• A visual-level Top N filter is often the simplest option when ranking is purely for display.

Note: RANKX evaluates the expression once per row of the table argument, so ranking a high-cardinality column such as invoice numbers with a complex measure can be slow. Rank at the grain the business actually needs.

A total row is not a sum of the rows above it. Power BI evaluates the measure again in the total's own filter context, where no single row value is filtered. For simple additive measures such as SUM the result happens to equal the sum of rows, but for conditional or non-additive logic it will not.

Classic example. A bonus is 10 percent of sales for salespeople who sell more than 10 lakh:

Bonus = IF ( [Total Sales] > 1000000, [Total Sales] * 0.1, 0 )

At the total row, [Total Sales] is the sum for everyone, which easily exceeds 10 lakh, so the total shows 10 percent of all sales, including salespeople who earned nothing. The fix is to calculate at the correct grain and then add up:

Bonus Total =
SUMX (
    VALUES ( Salesperson[Name] ),
    VAR s = [Total Sales]
    RETURN IF ( s > 1000000, s * 0.1, 0 )
)

SUMX iterates each salesperson, context transition gives each one their own sales, and the total is the sum of individual bonuses.

Tools for controlling totals:

• ISINSCOPE(column) tells you whether the current cell is at that level of the hierarchy, so you can return a different value, or blank, at totals.
• HASONEVALUE and SELECTEDVALUE check whether exactly one item is filtered.

Sometimes the total is correct and the expectation is wrong. A distinct count of customers will be lower in the total than the sum of rows, because a customer who bought in two regions is one customer. Averages and percentages are also correctly non-additive.

Note: The best interview answer starts with a question back: what should the total mean for the business? Once that is clear, the right grain for the SUMX becomes obvious.

A semi-additive measure can be summed across some dimensions but not across time. Stock on hand, bank balances and headcount are snapshots: adding the stock of each product is fine, but adding January, February and March stock produces a meaningless number.

Assume an Inventory table with one row per product per snapshot date, and a base measure Stock Qty = SUM(Inventory[Quantity]).

Closing balance for any period:

Closing Stock =
CALCULATE (
    [Stock Qty],
    LASTNONBLANK ( 'Date'[Date], [Stock Qty] )
)

LASTNONBLANK finds the latest date in the period that actually has a snapshot. This matters because LASTDATE('Date'[Date]) returns the calendar month-end, which may be a Sunday with no data, leaving the result blank.

Other patterns:

• CLOSINGBALANCEMONTH, QUARTER and YEAR, plus OPENINGBALANCEMONTH, are convenient shortcuts when snapshots exist for every calendar date.
• Average balance over a period is often needed for ratios such as inventory turnover:

  Average Stock =
  AVERAGEX ( VALUES ( 'Date'[Date] ), [Stock Qty] )

• Opening balance is usually the closing balance of the previous period: CALCULATE([Closing Stock], PREVIOUSMONTH('Date'[Date])).

Subtle point: if different warehouses submit snapshots on different days, a single last date for the whole selection may miss some of them. In that case, iterate the warehouses with SUMX and find each one's own last snapshot date.

Note: Recognising that a measure is semi-additive, and saying so, is often what separates candidates who have built finance or supply-chain models from those who have only built sales dashboards.

TREATAS takes a table of values and applies them as a filter on columns of another table, as if a relationship existed. It creates a virtual relationship that lives only inside the measure.

Budget Amount =
CALCULATE (
    SUM ( Budget[Amount] ),
    TREATAS ( VALUES ( Product[Category] ), Budget[Category] )
)

Here the Budget table has no relationship to Product, but whatever categories are selected through the Product dimension are pushed on to Budget[Category].

When it is the right tool:

• Different granularity. Budgets are often set by category and month while sales are by product and day. TREATAS avoids a many-to-many relationship.
• Avoiding ambiguity. A physical relationship would create a second filter path or require bidirectional filtering. A virtual one applies only where needed.
• Disconnected slicer tables. A parameter or scenario table can drive filters on a fact column. TREATAS can map several columns at once, such as year and month together.
• Replacing older patterns. TREATAS is cleaner and generally faster than filtering with CONTAINS or INTERSECT.

When not to use it:

• If a clean one-to-many relationship is possible, build it. Physical relationships are faster, and they work automatically for every measure and every visual.
• Virtual relationships are invisible in the model diagram, so document them in the measure description, or the next developer will not know they exist.

Note: TREATAS is a good sign of DAX depth in an interview. Pair it with the principle that the model should do the work wherever possible, and virtual relationships should be the exception.

A calculation group is a special table whose rows, called calculation items, each apply a transformation to whatever measure is currently in use. The measure is referenced with SELECTEDMEASURE().

The problem it solves is measure explosion. With 10 base measures and 6 time variations (current, MTD, YTD, prior year, YoY change, YoY %), you would otherwise need 60 near-identical measures. A calculation group keeps the 10 base measures and adds one Time Intelligence group:

Current = SELECTEDMEASURE ()
YTD     = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date], "3/31" ) )
PY      = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY %   =
    VAR Curr = SELECTEDMEASURE ()
    VAR Prev = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    RETURN DIVIDE ( Curr - Prev, Prev )

Put the calculation group column on a matrix's columns or in a slicer, and every measure gains all six variations.

Practical details:

• They can be created in Power BI Desktop's model view or with Tabular Editor.
• Format string expressions let YoY % display as a percentage even when the underlying measure is currency.
• Creating one sets discourage implicit measures, so users must use explicit measures rather than dragging a numeric column into a visual.
• Precedence controls the order of evaluation when two calculation groups apply at once, for example time intelligence and currency conversion.
• ISSELECTEDMEASURE and SELECTEDMEASURENAME let an item skip measures it should not change, such as a ratio that should not be YTD-summed.

Note: Calculation groups make a model easier to maintain but harder for casual users to understand. Name the items clearly and explain them on a help page in the report.

Both appear under Modeling, New parameter, and both create a table that drives a slicer, but they solve different problems.

Field parameters let users choose which field or measure a visual shows. Power BI creates a calculated table listing the chosen fields using NAMEOF:

Metric Selector = {
    ( "Revenue", NAMEOF ( [Total Sales] ), 0 ),
    ( "Profit", NAMEOF ( [Total Profit] ), 1 ),
    ( "Orders", NAMEOF ( [Order Count] ), 2 )
}

Put the parameter on a chart's axis or values, add it as a slicer, and one chart can switch between Revenue, Profit and Orders, or between Region, Product and Channel on the axis. This replaces the old bookmark-and-hidden-visual workarounds.

What-if parameters let users choose a number to feed into calculations. Power BI creates a table with GENERATESERIES and a measure that reads the selection:

Price Change = GENERATESERIES ( -0.2, 0.2, 0.05 )
Price Change Value = SELECTEDVALUE ( 'Price Change'[Price Change], 0 )

Projected Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] * ( 1 + [Price Change Value] ) )

A slider then lets a manager ask what revenue would look like with a 10 percent price rise, without touching the data.

Key differences:

• Field parameters change the structure of a visual. What-if parameters change the value of a calculation.
• What-if values only affect measures that reference them. They never change imported data.
• SELECTEDVALUE needs a sensible default, otherwise the projection breaks when nothing is selected.

Note: Pair either parameter with a dynamic title, such as a measure that concatenates the selected metric name, so screenshots of the report remain self-explanatory.

M is the functional formula language behind Power Query. Every click in the editor, such as filtering rows or changing a type, writes one step of M code. The Applied Steps pane is simply a view of the step names in that code.

let
    Source = Sql.Database ( "srv-prod-01", "SalesDB" ),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data],
    FilteredRows = Table.SelectRows ( Orders, each [OrderDate] >= #date ( 2024, 4, 1 ) ),
    RemovedColumns = Table.RemoveColumns ( FilteredRows, {"Notes", "CreatedBy"} ),
    ChangedType = Table.TransformColumnTypes ( RemovedColumns, {{"Amount", Currency.Type}} )
in
    ChangedType

How to read it:

• let ... in defines named steps, and in says which step is the output.
• Each step usually takes the previous step as its first argument, forming a chain.
• each [OrderDate] >= ... is shorthand for a function applied to every row.
• M is case-sensitive, so Table.selectrows fails, and a filter on "delhi" will not match "Delhi".
• Evaluation is lazy: Power Query works out what the final step needs and, where possible, folds it into a single source query.

Why an analyst should be comfortable in the Advanced Editor:

• Fixing a step that broke after a source column was renamed.
• Replacing hard-coded values with parameters, such as the server name for development and production.
• Writing custom functions and reusing logic across queries.
• Removing auto-generated steps, such as a Changed Type step that hard-codes every column name and breaks when a new column appears.

Note: Renaming steps meaningfully, as in the example above, is a small habit that interviewers notice. Default names such as Custom1 and Changed Type2 make a query almost impossible to maintain.

Query folding means Power Query translates your transformation steps into a single native query, usually SQL, that the source system executes. Only the finished result travels to Power BI.

Why it matters:

• Speed. Filtering 50 million rows down to 2 million in the database is far faster than downloading 50 million and filtering them in the mashup engine.
• Incremental refresh depends on it. The RangeStart and RangeEnd filter must fold, otherwise every partition pulls the whole table.
• DirectQuery requires it. Every step must fold, because there is no import stage to do the work.
• Lower gateway and memory load during refresh.

How to check it: right-click a step and look at View Native Query. If you can open it, that step folds. The step folding indicators in Power Query show the same at a glance. A greyed-out option is a strong hint, though not always proof, that folding has stopped.

Steps that usually fold against relational sources: removing columns, filtering rows, sorting, grouping, simple type changes, and merges between tables on the same database.

Steps that commonly break folding: adding an index column, many text transformations, merging queries from different sources, Table.Buffer, and custom functions the connector cannot translate. Once folding breaks, every later step runs locally too.

Best practice: put foldable steps such as filters and column removal first, and non-foldable steps last. When a transformation is complex, push it into a database view instead.

Note: Flat files such as CSV and Excel never fold because there is no engine to fold into. For large file-based sources, reducing columns and rows early matters even more.

They combine tables in different directions:

Append matches columns by name. A column missing in one table is filled with null, so inconsistent headers such as Amount and Amt produce two half-empty columns.

Merge join kinds:

• Left outer — all rows from the first table, matches from the second. The default.
• Right outer — all rows from the second table.
• Full outer — all rows from both.
• Inner — only rows that match in both.
• Left anti — rows in the first table with no match in the second. Ideal for reconciliation, such as orders without an invoice.
• Right anti — rows in the second table with no match in the first.

Fuzzy matching is also available, with a similarity threshold, for messy text keys like company names.

Pitfalls:

• Merges are case-sensitive and sensitive to trailing spaces, so trim and standardise keys first.
• Duplicate keys in the second table multiply rows after expansion, silently inflating totals.
• Merging every dimension attribute into the fact table creates a wide flat table. In Power BI, it is usually better to keep dimensions separate and relate them in the model.

Note: A good rule of thumb is to merge in Power Query when you are cleaning or reconciling data, but relate tables in the model when you are building a star schema for reporting.

The Folder connector, or SharePoint Folder for files in SharePoint Online or OneDrive, lists every file with its metadata. Choosing Combine and Transform makes Power Query generate several helper objects:

• A Sample File query that points at one file.
• A parameter that holds the sample file.
• A Transform Sample File query where you build the cleaning steps.
• A custom function generated from those steps and invoked once per file, with the results appended.

Any step you add to Transform Sample File is automatically applied to every file, so twelve monthly sales files become one clean table, and next month's file is picked up on refresh with no extra work.

Writing a function yourself is straightforward once you know the pattern:

(FilePath as text) as table =>
let
    Source = Csv.Document ( File.Contents ( FilePath ), [Delimiter = ",", Encoding = 65001] ),
    Promoted = Table.PromoteHeaders ( Source, [PromoteAllScalars = true] )
in
    Promoted

Parameters are named values such as a folder path or server name. Changing one value switches the whole report from a test folder to production, and parameters can be edited in the Service without opening Desktop.

Pitfalls to mention:

• Stray files. Filter by extension and exclude temporary files starting with ~$, which appear while someone has a workbook open.
• Schema drift. If one month's file renames a column, the auto-generated Changed Type step fails. Standardise headers inside the function.
• Lineage. Keep the Source.Name column so every row can be traced back to its file.
• Gateways. A local or network folder needs an on-premises gateway to refresh in the Service. SharePoint Online does not.

Note: SharePoint Folder is slow when a site holds thousands of files, because it lists the whole site before filtering. Filter on the folder path as the very first step.

A dataflow is Power Query running in the Power BI Service or Microsoft Fabric rather than inside a single .pbix file. It connects to sources, applies transformations, and stores the result as reusable tables that many semantic models can load.

• Dataflow Gen1 stores its output in Azure Data Lake Storage managed by Power BI.
• Dataflow Gen2 in Fabric can write to a lakehouse or warehouse and be orchestrated in data pipelines.

When a dataflow is the right choice:

• Reuse. Five reports each clean the same customer master in their own Power Query, with five slightly different results. One dataflow gives one cleaned Customer table that all five consume.
• Protecting the source. A slow ERP or API is queried once by the dataflow, not once per report refresh.
• Separating roles. A data engineer owns the ETL, while report developers focus on modelling and DAX.
• Self-service without database access. Analysts get curated tables even when they are not allowed to connect to the source directly.
• Staging history. Dataflows can accumulate data from sources that only expose recent records.

When Desktop Power Query is fine: a single report with its own sources and no reuse. A dataflow then just adds another moving part.

Operational points:

• Refresh ordering. The dataflow must finish before the semantic model refreshes, otherwise the model loads yesterday's data. Schedule with a buffer, or chain them in a pipeline.
• Linked and computed tables, and the enhanced compute engine, need Premium, PPU or Fabric capacity.
• Dataflows do not replace a proper data warehouse for very large volumes or complex history such as slowly changing dimensions.

Note: In interviews, describe dataflows as the answer to logic duplicated across reports. That problem framing lands better than a description of storage options.

A composite model mixes storage modes in one semantic model: some tables in Import, some in DirectQuery, and some in Dual. It is the way to get near-Import speed on data too large or too fresh to import fully.

Aggregation tables are the main use case. Suppose a Sales fact of 3 billion rows is kept in DirectQuery on a warehouse. You import a much smaller table summarised by day, product and store, then map it to the detail table in Manage aggregations:

• A visual showing sales by month and category is answered from the in-memory aggregate in milliseconds.
• A drill down to individual invoices is automatically sent to the warehouse as a DirectQuery.
• Users never choose between the two. The engine redirects each query on its own.

Dual storage mode is set on the shared dimension tables such as Date, Product and Store. A Dual table is held in memory and can also act as DirectQuery:

• With the imported aggregate, it behaves as Import, so the whole query stays in memory.
• With the DirectQuery fact, it behaves as DirectQuery, so the join happens in the source rather than as a slow cross-source limited relationship.

Another form of composite model is DirectQuery to a published Power BI semantic model. You connect to a certified shared model and add your own local tables, such as a target spreadsheet, without copying the central model.

Things to watch:

• Relationships across different source groups are limited relationships, which are slower and behave differently with blank keys.
• Dual tables still need refreshing, and must stay in sync with the source to avoid mismatched results.
• Data from one source may be sent to another inside queries, which matters for data governance.

Note: Use Performance Analyzer or DAX Studio to confirm that queries actually hit the aggregation. A mapping at the wrong grain silently sends everything to DirectQuery.

Incremental refresh partitions a large table by date so the Service refreshes only recent data. Configuring it has three steps.

1. Create two Date/Time parameters named exactly RangeStart and RangeEnd. The names are case-sensitive and reserved.
2. Filter the date column with them, with one boundary inclusive and the other exclusive:

   Filtered = Table.SelectRows (
       Orders,
       each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
   )

3. Define the policy on the table in Desktop. For example, archive 5 years and incrementally refresh the last 10 days. Optional settings include Detect data changes, which refreshes a partition only if the maximum of a last-updated column has changed, Only refresh complete periods, and a real-time DirectQuery partition for the latest data, creating a hybrid table on Premium capacity.

After publishing, the first refresh in the Service creates all the partitions and loads the full history, so expect it to be slow. Later refreshes touch only the recent partitions.

What commonly goes wrong:

• Using greater-than-or-equal on both ends. Rows exactly on a boundary land in two partitions and are double counted.
• The filter does not fold. Each partition then pulls the whole table before filtering, making refresh slower than before.
• Integer date keys. If the source stores dates as numbers such as 20250401, write a small function to convert RangeStart and RangeEnd to that format rather than converting the column, which breaks folding.
• Expecting to download the file again. Once incremental refresh has run in the Service, the model cannot be downloaded as a .pbix. Keep the source file under version control.
• Late-arriving updates older than the refresh window are never picked up unless you widen the window or refresh old partitions via the XMLA endpoint.

Note: In Desktop, set the parameters to a small range, such as one month, so development stays fast. The Service replaces the values with partition boundaries at refresh time.

Row-level security (RLS) restricts which rows a user sees. A regional manager sees only their region's sales, but every table and column still exists for them.

Object-level security (OLS) restricts which tables or columns a user can see at all. For users in a restricted role, a secured Salary column or a whole Payroll table effectively does not exist. It is hidden from the field list, cannot be queried from Excel or DAX, and its metadata is not exposed.

How it is configured:

• Create roles in Power BI Desktop as you would for RLS.
• Set table or column permissions to None for the role with an external tool such as Tabular Editor, or through the XMLA endpoint. The Desktop role editor is designed for row filters, not object permissions.
• Publish and assign users or security groups to the roles in the Service.

Behaviour to understand:

• A visual that uses a secured object shows an error for restricted users, so either build role-specific pages or use a separate report for them.
• Measures that reference a secured column are also secured automatically, so they cannot leak the data indirectly.
• RLS and OLS cannot be combined across different roles for the same user. If a user needs both, define them in the same role.
• Like RLS, OLS applies only to users with Viewer access. Workspace Admins, Members and Contributors see everything.

Typical use cases: hiding salary, cost price or margin columns from sales staff, and hiding personal data such as phone numbers from general users while analysts still see aggregates.

Note: Hiding a column in the model view is not security. It only removes it from the field list, and anyone with build access can still query it. OLS is the feature that actually enforces the restriction.

The on-premises data gateway is software installed inside the corporate network that lets the Power BI Service reach sources the cloud cannot see directly, such as an on-premises SQL Server, Oracle, file shares or local Excel files. It makes outbound connections only, so no inbound firewall ports are needed.

Standard mode is the right choice for any production report. Personal mode suits individual analysts experimenting on their own machine.

Cloud sources such as Azure SQL Database, SharePoint Online and Dataverse do not need a gateway. Sources inside an Azure virtual network can use the VNet data gateway instead.

Troubleshooting refresh failures:

• Gateway offline: the host machine was restarted, went to sleep, or lost network access. Install it on an always-on server, never on a laptop, and cluster two machines.
• Credential errors: passwords expired on the data source connection. Use a service account with a non-expiring password policy.
• Path mismatch: the path in the data source settings must exactly match the one used in Desktop, and a UNC path is safer than a mapped drive letter.
• Slow refresh: install the gateway close to the data source, give it enough memory, and check gateway performance logs.
• Version drift: keep the gateway updated, because very old versions stop being supported.

Note: Mention that the gateway should be owned by a team, with at least two admins. Gateways tied to one employee's account become a common single point of failure when that person leaves.

Performance Analyzer (View ribbon) records what happens when each visual loads. Start recording, click Refresh visuals, and every visual reports three timings:

• DAX query — time for the engine to calculate the result. A high value points to the model or the measures.
• Visual display — time to render. A high value points to too many data points, heavy custom visuals, or complex conditional formatting.
• Other — time spent waiting, mostly for other visuals ahead in the queue. A high value across the page usually means too many visuals.

Always test with a cold cache as well. The first click after opening is what users actually experience.

For a slow DAX query, use Copy query and paste it into DAX Studio. Turn on Server Timings and run it with a cleared cache. You then see how the time splits between the two engines:

• Storage engine (VertiPaq) — multi-threaded, fast scans of compressed columns, with results that can be cached.
• Formula engine — single-threaded, handles complex logic, row-by-row iteration and callbacks.

Healthy queries spend most of their time in a small number of storage engine scans. Warning signs are:

• A large share of time in the formula engine.
• Dozens of storage engine queries for one visual, often caused by an iterator calling a measure over a large table.
• Storage engine queries containing CallbackDataID, which means the storage engine is calling back to the formula engine row by row, for example because of an IF inside SUMX.
• Very large intermediate row counts, which point to missing filters or a high-cardinality column.

After each change, rerun the same query and compare timings, so every optimisation is proven rather than assumed.

Note: DAX Studio's VertiPaq Analyzer view complements this. It shows which columns take the most memory, and high-cardinality columns are frequently the root cause of slow scans.

Deployment pipelines are a Power BI Service and Fabric feature, requiring Premium, PPU or Fabric capacity, that promote content through stages. By default there are Development, Test and Production, and each stage is linked to its own workspace.

How they work:

• Developers publish to the Development workspace.
• The pipeline compares stages and highlights new, changed and missing items. You then deploy all content or selected items to Test, where business users validate it.
• After sign-off, the same items are deployed to Production, where the app is published for end users.
• Deployment copies metadata, not data. A semantic model deployed to a new stage needs a refresh before it shows anything.

Deployment rules are what make it practical. They override data source connections or parameter values per stage, so the Development model points at the dev database and the Production model at the live one, without anyone editing the file.

Benefits:

• No more editing reports directly in production.
• A clear approval step before users see changes.
• A history of deployments, with the ability to see who deployed what.

Complementary practices:

• Source control: save reports in the Power BI Project (.pbip) format and use Fabric Git integration with Azure DevOps or GitHub, so changes can be reviewed and rolled back.
• Automation: pipelines can be triggered through REST APIs from CI/CD tools.
• Without Premium: use separate dev and prod workspaces, parameterised connections, and a documented manual release checklist.

Note: Interviewers for senior roles often ask how you prevent someone from breaking production. Deployment pipelines combined with Viewer-only access for most users in the production workspace is a strong, concise answer.

A workspace has four roles, each including everything in the role below it:

• Viewer — view and interact with content only. RLS and OLS apply.
• Contributor — create, edit, publish and delete content, and schedule refreshes. Can update the app only if an admin allows it.
• Member — everything a Contributor can do, plus sharing items, adding other Members, Contributors and Viewers, and publishing or updating the app.
• Admin — full control, including adding other admins, changing settings and deleting the workspace.

A critical point: RLS does not apply to Admins, Members or Contributors. Anyone who should see only their own rows must be a Viewer or receive the content through an app.

Ways to give access to consumers:

• Apps — the preferred route for broad distribution. The app packages selected reports with navigation, and audiences show different content to different groups. Users never need workspace access.
• Direct sharing and links — convenient for a few people, but hard to audit at scale.
• Embedding in Teams, SharePoint or a custom application.

Build permission is set on the semantic model. It lets users create their own reports on the model, use Analyze in Excel and query it, without being able to edit the model or the original reports. This is how self-service is enabled safely on top of a governed model.

Good practice:

• Assign roles to Entra ID (Azure AD) security groups, not individuals, so joiners and leavers are handled by HR processes.
• Keep the Admin role to two or three people.
• Keep creators in the workspace and consumers in the app.

Note: Viewers need a Pro or PPU licence unless the workspace is on Premium or Fabric F64 or higher capacity, in which case free-licence users can view content. Mentioning this shows awareness of licensing in real deployments.

A shared semantic model, previously called a dataset, is published once and used by many reports. A thin report contains only pages and visuals, with no data of its own, and connects live to that shared model.

Why this architecture is recommended:

• Single source of truth. Revenue, margin and customer counts are defined once, so every report shows the same number.
• One refresh. The data is loaded once instead of once per report, reducing load on sources and capacity.
• Separation of roles. A small team owns and tests the model, while many analysts build reports on it.
• Smaller files that are faster to publish and easier to maintain.

Endorsement tells users which content to trust:

• Promoted — any owner with write access can mark content as ready for wider use.
• Certified — only reviewers authorised by the tenant administrator can apply it, after the content meets organisational standards.
• Endorsed models rank higher in the OneLake data hub, so report builders find the right model first.

Governance practices:

• Keep models and reports in separate workspaces, and grant Build permission to report creators rather than edit rights.
• Use lineage view and impact analysis before changing a model. Renaming a measure used by 30 thin reports breaks all 30.
• Add descriptions to measures and organise them in display folders so report builders understand them.
• When a report needs extra data, use a composite model with DirectQuery to the semantic model instead of copying the entire model.

Note: A good interview line is that the shared model is a product with users. It needs versioning, testing, documentation and change communication just like any software product.

Start from the question the page must answer and the audience, then choose visuals. A page for the CFO and a page for a regional sales manager look very different even on the same data.

Layout:

• One page, one purpose. Put KPI cards at the top left, where eyes land first, trends next, and detail at the bottom or on a drillthrough page.
• Keep it to roughly 6 to 10 visuals. More visuals are harder to read and slower, because each one sends its own queries.
• Align visuals to a grid, use consistent spacing, and group slicers in one place or in a collapsible filter pane.

Choosing the visual:

• Trend over time: line chart.
• Comparison across categories: bar or column chart, sorted by value, with the axis starting at zero.
• Part of a whole: a stacked bar or a donut only with two to four segments. Beyond that a sorted bar is clearer.
• Actual against target: KPI visual, bullet chart or card with variance.
• Relationship between two measures: scatter chart.
• Exact values for lookup: table or matrix, ideally with conditional formatting.
• Maps only when geography genuinely matters to the decision.

Formatting:

• Use colour with meaning. For example, a single highlight colour for the focus, and red only for bad performance.
• Apply a JSON theme so every report follows the brand palette.
• Format numbers for the audience, such as rupees in lakh or crore for Indian business users, and remove unnecessary decimals, gridlines and borders.
• Write titles that state the insight, such as North region missed target in Q3, not just Sales by Region.

Accessibility: check contrast, add alt text, set the tab order, and never rely on colour alone to convey status.

Note: Always ask whether a report will be viewed on phones. The mobile layout must be designed separately in Power BI Desktop, and many executives only ever open reports on their phone.