Power BI Developer Interview Questions &amp; Answers

Show answer

I would build dynamic RLS with a security table of Email and Region, related to the Region dimension, and a role filter Security[Email] = USERPRINCIPALNAME(). The CEO and other full-access users get an 'All' flag handled in the rule, or are placed in a separate role with no filter. I would test with View as role in Desktop and in the Service before publishing the app.

Scheduled refresh fails every night with a gateway error. How do you troubleshoot?

Show answer

I would check the refresh history error details, confirm the gateway machine is on and the gateway service is running and updated, and verify data source credentials in the gateway connection have not expired. Then test connectivity from the gateway machine to the source and check that the source names in the model exactly match the gateway data source. Gateway logs and network or firewall changes are next.

Your model has one wide flat table with 80 columns from an Excel export. How would you remodel it?

Show answer

I would identify the grain, such as one row per order line, and split descriptive attributes into dimension tables like Customer, Product, Store and Date using Reference queries and Remove Duplicates on keys. The fact table keeps keys and numeric columns only. Then create one-to-many single-direction relationships. This reduces size, simplifies DAX and makes slicers faster and more reliable.

What is the difference between SAMEPERIODLASTYEAR, DATEADD and PARALLELPERIOD?

Show answer

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

What is a role-playing dimension and how do you handle it?

Show answer

A role-playing dimension is one dimension used in several roles, such as Date acting as Order Date, Ship Date and Delivery Date. Options are one Date table with one active and other inactive relationships used through USERELATIONSHIP in measures, or separate copies of the date table for each role so users can slice by each date independently. The second is easier for users but increases model size slightly.

Business wants year-over-year growth, but the current year is incomplete. How do you compare fairly?

Show answer

Comparing a partial year with a full last year understates growth. I would limit last year to the same date range, for example using the last date with sales this year and filtering last year's dates up to the matching day, or compare year-to-date to previous year-to-date. I would label the visual clearly as YTD versus same period last year so readers understand it.

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

Show answer

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

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

Show answer

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

What are deployment pipelines?

Show answer

Deployment pipelines move content through stages, typically Development, Test and Production workspaces. Creators deploy reports and semantic models from one stage to the next, compare differences, and use deployment rules to change data source connections or parameters for each stage. This supports controlled releases and testing before users see changes. They work alongside Git integration for version control in Fabric workspaces.

What is KEEPFILTERS?

Show answer

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

What are the most useful table functions in DAX?

Show answer

Key table functions include FILTER, ALL, VALUES, DISTINCT, SUMMARIZE and SUMMARIZECOLUMNS for grouping, ADDCOLUMNS to add calculated columns, TOPN for top rows, CROSSJOIN, UNION, EXCEPT, INTERSECT, GENERATESERIES and TREATAS for virtual relationships. They return tables used inside iterators, CALCULATE filters or calculated tables. ADDCOLUMNS over SUMMARIZE is a common pattern for reliable grouped results.

What is cross-filter direction and why is bi-directional filtering risky?

Show answer

Cross-filter direction controls which way filters travel across a relationship. Single direction, from the one side to the many side, is the default and safest. Bi-directional lets the fact table filter the dimension too, which can be useful for slicers showing only relevant values, but it can cause ambiguous paths, unexpected results and slower queries. Prefer single direction and use CROSSFILTER inside specific measures when needed.

Write a 3-month rolling average measure.

Show answer

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

What is a calculated table and when would you use one?

Show answer

A calculated table is created with a DAX expression and stored in the model, recalculated at refresh. Examples are a date table using CALENDAR, a disconnected parameter table for what-if analysis, or a summary table. It is handy but adds memory, and it cannot use Power Query folding. For most shaping tasks, creating tables in the source or Power Query is preferred.

Why should you use DIVIDE instead of the division operator?

DAX FundamentalsEasy

Show answer

DIVIDE(numerator, denominator, alternate) handles division by zero and blank denominators safely, returning blank or the alternate value instead of an error or infinity. It makes measures like margin percentage cleaner without IF checks. The division operator is slightly faster when you are certain the denominator is never zero, but DIVIDE is the safer default in reports.

What are endorsement, sensitivity labels and lineage view?

Show answer

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

Explain how RANKX works and common mistakes with it.

Show answer

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

What is time intelligence in DAX?

Advanced DAX and Time IntelligenceEasy

Show answer

Time intelligence functions compare values across periods using a proper date table, for example year-to-date, same period last year, moving averages and quarter-over-quarter growth. Key functions are TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD and DATESINPERIOD. They return sets of dates used as CALCULATE filters, so the date table must be contiguous, marked as a date table and related to the facts.

What is the difference between SUM and SUMX?

DAX FundamentalsEasy

Show answer

SUM aggregates a single column, such as SUM(Sales[Amount]). SUMX is an iterator: it evaluates an expression for each row of a table and then sums the results, such as SUMX(Sales, Sales[Qty] * Sales[Price]). Use SUMX when you need row-level calculations before aggregating. Other iterators include AVERAGEX, MAXX, COUNTX and RANKX.

Tell me about a time you handled a data access or security issue.

Power BI Service, Sharing and SecurityHard

Show answer

Situation: a sales user reported seeing another region's numbers after a reorganisation. Task: fix the leak fast. Action: I confirmed the security mapping table had stale entries, removed the user from the wrong row, refreshed the model and then automated the mapping from the HR system with a daily refresh. I informed the data owner. Result: access stayed correct after later reorganisations, and quarterly access reviews were added.

Write a dynamic RLS rule using a security mapping table.

Show answer

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

Write a measure for sales of a specific category and region.

DAX FundamentalsEasy

Show answer

Electronics North Sales = CALCULATE( [Total Sales], 'Product'[Category] = "Electronics", 'Store'[Region] = "North" ) Multiple filter arguments are combined with AND. Wrap a filter with KEEPFILTERS if it should intersect with an existing slicer selection instead of replacing it.

Tell me about a time you redesigned a data model.

Show answer

Situation: a finance report took 40 seconds per page with a 2 GB file built on joined flat tables. Task: improve speed. Action: I split it into a star schema with Date, Account, Cost Centre and fact tables, removed unused columns, turned off auto date/time and replaced bi-directional filters with single direction. Result: model size dropped to 450 MB and pages loaded in under 4 seconds.

What is context transition?

DAX FundamentalsHard

Show answer

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

Users want to analyse sales by both order date and delivery date in the same report. What do you do?

Show answer

I would keep the Date table related to OrderDate as the active relationship and add an inactive relationship to DeliveryDate. Then create measures like Sales by Delivery Date using USERELATIONSHIP. If users need separate slicers for both dates at the same time, I would create a second Delivery Date table as a role-playing copy related actively to the delivery column.

What is the difference between SELECTEDVALUE and VALUES?

Show answer

SELECTEDVALUE(column, alternate) returns the single value of a column when exactly one is visible in the filter context, otherwise the alternate or blank. VALUES(column) returns a table of distinct visible values, including blank for unmatched rows, and can be used in iterators or as a filter. Use SELECTEDVALUE for titles and parameters, and VALUES when you need to loop over the visible items.

Tell me about a time you found a wrong number in a published report.

Show answer

Situation: a regional head noticed our margin report did not match finance. Task: find and fix it quickly. Action: I compared numbers at product level, found returns were double-subtracted because a measure used both a net amount column and a returns table. I fixed the measure, added a reconciliation page against finance totals and informed users. Result: numbers matched to the rupee and the reconciliation check became standard.

What is the relationship between Power BI and Microsoft Fabric?

Power BI FundamentalsMedium

Show answer

Microsoft Fabric is Microsoft's unified analytics platform that includes data engineering, data factory, data warehousing, real-time analytics and Power BI as its reporting layer. Power BI workspaces can live on Fabric capacity and use items such as lakehouses and warehouses in OneLake. Direct Lake mode lets semantic models read Delta tables in OneLake directly without a full import, combining fresh data with near-import speed.

Your calculated columns have made the model huge. What do you do?

Show answer

I would move simple row-level logic into Power Query or the source SQL, where it compresses better and can fold, and replace aggregations stored as columns with measures. High-cardinality columns such as concatenated keys or precise timestamps should be removed or split. Then check model size with VertiPaq Analyzer in DAX Studio to confirm which columns used the most memory.

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

Show answer

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

What is the difference between FILTER and a simple boolean filter in CALCULATE?

DAX FundamentalsHard

Show answer

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

Create a calculated column that categorises customers by their lifetime sales.

Show answer

Customer Band = VAR s = CALCULATE(SUM(Sales[Amount])) RETURN SWITCH(TRUE(), s >= 500000, "Platinum", s >= 100000, "Gold", s > 0, "Silver", "No Purchase") This is a column on the Customer table; CALCULATE triggers context transition so each row gets its own customer's sales.

Write a measure that counts customers with sales above 10,000.

Show answer

Big Customers = COUNTROWS( FILTER( VALUES(Customer[CustomerID]), [Total Sales] > 10000 ) ) Iterating VALUES of the customer key keeps the loop small and respects current filters such as year or region.

What is the difference between static and dynamic RLS?

Show answer

Static RLS hard-codes values in each role, such as one role per region, which becomes hard to manage as regions grow. Dynamic RLS uses one role with a filter based on the logged-in user, such as Users[Email] = USERPRINCIPALNAME(), with a security mapping table linking users to the regions or managers they may see. Dynamic RLS scales better and is driven by data rather than role edits.

The total row of a table shows a different value than the sum of rows. Is this a bug?

Show answer

Usually not. A measure's total is calculated in the total's own filter context, not by adding the visible rows. For non-additive measures like averages, distinct counts or ratios this differs from the row sum. If business wants the sum of rows, I write it explicitly with SUMX over the row values, for example SUMX(VALUES('Product'[Product]), [My Measure]).

Tell me about a time you had to learn a new BI tool or feature quickly for a project.

Power BI FundamentalsMedium

Show answer

Situation: a client moved its data to a Fabric lakehouse and wanted reports on Direct Lake within three weeks. Task: learn it and deliver. Action: I studied the documentation, built a small proof of concept in two days, checked fallback behaviour and refresh rules, and then migrated the main sales model. Result: reports went live on time with data visible minutes after loading instead of after nightly refreshes.

Explain relationship cardinality in Power BI.

Data ModellingEasy

Show answer

Cardinality describes how keys match between two tables. One-to-many is the normal type, from a dimension's unique key to a fact table's repeating key. One-to-one links tables with unique keys on both sides and often means they should be merged. Many-to-many allows duplicates on both sides; it is supported but can produce confusing totals, so a bridge table is often clearer.

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

Show answer

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

How do you handle a fiscal year in time intelligence functions?

Show answer

Functions like TOTALYTD and DATESYTD accept a year-end date argument, so for an Indian fiscal year ending 31 March use TOTALYTD([Sales], 'Date'[Date], "31/3"). Add fiscal year, quarter and month columns to the date table for slicing. For complex calendars such as 4-4-5 retail weeks, standard functions do not work, and you write custom logic using fiscal period columns. Newer calendar-based time intelligence also supports custom calendars.

What is a snowflake schema and should you avoid it in Power BI?