Power BI Interview Questions for 7 Years Experience
40 questions with answers for candidates with 7 years of experience — 22 medium, 18 hard.
What interviewers expect: 7 years experience
Seven-year candidates are considered for lead, consultant or architect roles. Discussions centre on designing BI platforms on Microsoft Fabric, Direct Lake, capacity management, embedding for external users, CI/CD and data governance with sensitivity labels. Interviewers look for clear trade-off reasoning, delivery leadership and examples of measurable business impact from reporting programmes.
This set is weighted towards Performance Optimisation and Administration (10), Advanced DAX and Time Intelligence (8), Data Modelling (5), DAX Fundamentals (4).
Fabric architecture Embedded analytics CI/CD and governance Delivery leadership
Solved at this levelStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is the difference between SAMEPERIODLASTYEAR, DATEADD and PARALLELPERIOD?
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.
02
Write a measure that counts customers with sales above 10,000.
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.
03
Explain how RANKX works and common mistakes with it.
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.
04
A manager must see their whole reporting hierarchy, not just their own team. How do you build RLS for that?
I would flatten the employee hierarchy with PATH(Employee[EmployeeID], Employee[ManagerID]) into a path column, then in the role filter use PATHCONTAINS(Employee[Path], LOOKUPVALUE(Employee[EmployeeID], Employee[Email], USERPRINCIPALNAME())). Each manager then sees every employee below them at any depth. I would test with a few known hierarchies and check performance on a large employee table.
Use sufficient colour contrast and do not rely on colour alone, for example add icons or labels to red and green. Set alt text on visuals, a logical tab order in the selection pane, clear titles and readable font sizes. Avoid tiny text and very dense pages. Power BI supports screen readers and keyboard navigation, and high-contrast themes help users with low vision.
TREATAS applies the values of one table or column as filters on columns of another table, creating a virtual relationship without a physical one. For example, CALCULATE([Budget], TREATAS(VALUES('Product'[Category]), Budget[Category])) filters budget by selected categories even when no relationship exists. It is useful for different-grain tables or disconnected slicers, though physical relationships usually perform better.
Calculation groups are reusable sets of calculation items that modify any measure, such as Current, YTD, Previous Year and YoY %, using SELECTEDMEASURE(). Instead of writing YTD versions of 30 measures, you write one item that applies to all. They can be created in Power BI Desktop model view or Tabular Editor. They reduce measure sprawl but change how measures behave, so naming and format strings need care.
08
Reports slow down every morning at 9 a.m. on shared capacity. What could be happening?
Morning peaks often combine many users opening reports with heavy scheduled refreshes running at the same time. I would check the Fabric Capacity Metrics app for throttling and identify the heaviest items. Fixes include moving refreshes to off-peak hours, optimising the largest semantic models, spreading workloads across capacities, or scaling the capacity. Communicating refresh windows to teams also helps.
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.
10
Rewrite a slow measure to use variables and avoid repeated calculation.
Before:
Growth = IF([Sales PY] = 0, BLANK(), ([Total Sales] - [Sales PY]) / [Sales PY])
After:
Growth =
VAR cur = [Total Sales]
VAR py = [Sales PY]
RETURN DIVIDE(cur - py, py)
The previous year measure is now evaluated once instead of three times, and DIVIDE handles zero safely.
11
Write an M query that filters rows, removes columns and changes types.
let
Source = Sql.Database("server", "SalesDB"),
Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
Filtered = Table.SelectRows(Orders, each [OrderDate] >= #date(2024, 1, 1)),
Kept = Table.SelectColumns(Filtered, {"OrderID", "OrderDate", "CustomerID", "Amount"}),
Typed = Table.TransformColumnTypes(Kept, {{"Amount", Currency.Type}, {"OrderDate", type date}})
in
Typed
All these steps fold to SQL on a database source.
12
A matrix with 200,000 rows makes the report slow and unusable. What would you suggest?
Large detail tables are rarely read on screen. I would understand the real need: if users want to search specific records, add slicers or a search filter and a drill-through page; if they need to export, provide a paginated report or analyse in Excel. On the main page I would show aggregated results with top N filters, which improves both speed and readability.
13
How do you mentor junior Power BI developers in your team?
I pair with them on real tasks, review their models and DAX with clear reasons rather than just fixes, and share a short checklist covering star schema, naming, measures over columns and performance checks. I give them ownership of small reports early. One junior I mentored moved from building single-table reports to owning a shared finance model within six months.
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.
15
Each regional manager must see only their region, and the CEO must see everything. How do you implement this?
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.
16
How do you handle a fiscal year in time intelligence functions?
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.
17
A report page takes 25 seconds to load. Walk me through how you would fix it.
I would run Performance Analyzer to find the slowest visuals, copy their DAX queries into DAX Studio and check server timings. Common fixes are rewriting measures that iterate large fact tables, removing bi-directional relationships, reducing visuals on the page, and shrinking the model by removing high-cardinality columns. If the source is DirectQuery, I would add aggregations or move to Import. Then re-measure after each change.
Surrogate keys are system-generated integer keys, such as ProductKey, used in dimensions instead of business keys like product codes. They handle changes in business keys, support Type 2 history with multiple rows per business key, and integer keys compress and join better in VertiPaq than long text keys. They are usually created in the data warehouse layer.
19
A DirectQuery report is slow and the database team complains about load. What do you do?
I would check which queries are slow using Performance Analyzer and the database's query logs. Then reduce visuals per page, add Import-mode aggregation tables for common summary views, set dimensions to Dual mode, and apply query reduction options so slicers send queries only on Apply. On the source side, indexes or columnstore tables help. If real-time is not essential, moving to Import with frequent refresh may be simpler.
20
A colleague's measure uses FILTER(Sales, ...) on a 50-million-row table and is slow. How would you rewrite it?
I would replace FILTER over the whole fact table with column filters in CALCULATE, such as CALCULATE([Sales], Sales[Channel] = "Online"), which the engine can apply efficiently. If a condition uses a measure, iterate over a small dimension table instead, like FILTER(VALUES(Customer[CustomerID]), [Sales] > 10000). Then test in DAX Studio with server timings before and after.
21
How do you control how visuals interact with each other?
By default, selecting a data point cross-filters or cross-highlights other visuals on the page. Use Format, Edit interactions to choose filter, highlight or none for each visual pair, for example stopping a slicer from affecting a target KPI card. Slicers can also be synced across pages with the Sync slicers pane. Clear interaction design prevents confusing behaviour.
22
Tell me about a time you improved the performance of a slow report.
Situation: a sales report on DirectQuery took over 30 seconds per page and users had stopped opening it. Task: get pages under 5 seconds. Action: I added an Import aggregation table at day and store level, set dimensions to Dual, reduced the main page from 18 to 9 visuals and rewrote two FILTER-heavy measures. Result: page load fell to about 3 seconds and weekly active users doubled.
23
What tools do you use to analyse Power BI performance?
Performance Analyzer in Desktop records how long each visual takes for DAX query, visual display and other work, and lets you copy the query. DAX Studio runs those queries with server timings and query plans and includes VertiPaq Analyzer for model size by table and column. Best Practice Analyzer in Tabular Editor flags modelling issues. In the Service, capacity metrics apps show load on Premium or Fabric capacity.
Object-level security hides entire tables or columns from specific roles, so users in that role cannot see or query them, for example hiding a Salary column from non-HR users. It is configured with Tabular Editor or similar external tools on the model's roles. Unlike RLS, which filters rows, OLS removes metadata access. Visuals using a secured object show an error for restricted users.
25
What is a snowflake schema and should you avoid it in Power BI?
A snowflake schema normalises dimensions into several related tables, such as Product linked to Subcategory linked to Category. It saves little space in Power BI because columnar compression already handles repetition, and it adds relationships, longer filter paths and a more confusing field list. Usually it is better to flatten these into one Product dimension in Power Query, keeping the model a clean star.
Query folding is when Power Query translates transformation steps into a native source query, such as SQL, so the source does the work and only the result is loaded. It makes refreshes much faster and is required for incremental refresh and efficient DirectQuery. Steps like filtering, removing columns and grouping usually fold on SQL sources, while some custom M functions or adding an index column can break folding.
Parameters are named values used inside queries, such as server name, database, file path or a date range. They let you switch from a development to a production database without editing each query, and they are required for incremental refresh through RangeStart and RangeEnd. Parameters can be changed in the Service under semantic model settings, which helps deployment pipelines between environments.
28
How do you handle a many-to-many relationship between sales and budget at different grains?
Budgets are often at month and category level while sales are daily and per product. Instead of a direct many-to-many link, relate both fact tables to shared dimensions at the right level: Date and a Category dimension. Budget relates to Category and to Date through a month-level key or the first day of month. Measures then compare them, and visuals should use only the shared grain.
29
What is the difference between SELECTEDVALUE and VALUES?
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.
30
A large model's measures are fast in Desktop but slow in the Service. What could explain it?
Desktop runs on a local machine with its own memory and only you as user, while the Service shares capacity with other workloads and users, so throttling or memory pressure may appear. Also check RLS, which adds filters that do not exist in Desktop tests, gateway latency for DirectQuery, and differences in data volume after a full refresh. Testing with View as role and capacity metrics helps pinpoint it.
31
Your organisation has 500 report viewers. How would you plan licensing?
Giving all 500 viewers Pro licences is one option. If there are many viewers and heavy models, Fabric capacity at F64 or above lets free-licence users view content in workspaces on that capacity, while report creators still need Pro or PPU. I would compare costs, workload size and features like larger models with IT, and recommend the option that fits usage, without assuming one choice fits all.
32
How do you use a bridge table for a many-to-many scenario?
For example, accounts can have multiple customers and customers multiple accounts. Create a bridge table with unique Account and Customer pairs. Relate Customer to Bridge one-to-many, and Account to Bridge one-to-many, with the Bridge to Account relationship set to filter in both directions or use CROSSFILTER in measures. Facts relate to Account. Test totals carefully because shared accounts count once per customer.
Product Rank =
IF(
ISINSCOPE('Product'[Product]),
RANKX(ALLSELECTED('Product'[Product]), [Total Sales], , DESC, DENSE)
)
ALLSELECTED ranks within the products visible after slicers, and ISINSCOPE hides the rank on total rows.
34
What is Copilot in Power BI and what should you watch out for?
Copilot in Power BI uses generative AI to help create report pages, summarise visuals in narrative form, suggest DAX and answer questions about data in natural language. It needs supported capacity and tenant settings enabled by admins. Results depend heavily on a clean model with clear names, descriptions and synonyms, and outputs must be checked for correctness before sharing, especially generated DAX.
35
What are aggregation tables and how do they improve performance?
Aggregation tables store pre-summarised data, such as sales by day, product and store, in Import mode, while detail stays in a large DirectQuery table. With Manage aggregations, Power BI automatically answers queries from the small aggregation when the requested grain allows it and falls back to detail otherwise. This gives fast summary visuals on billion-row data while still allowing drill to detail.
36
How do you set up a custom function in Power Query to apply logic to many files?
Write a query for one file, turn the file path into a parameter, then right-click and Create Function. Or write it in M:
(path as text) as table =>
let
Src = Excel.Workbook(File.Contents(path), true),
Sheet = Src{[Item="Sales", Kind="Sheet"]}[Data]
in
Sheet
Then invoke it on a column of file paths with Add Column, Invoke Custom Function and expand the results.
37
Split a datetime column to reduce cardinality in Power Query.
= Table.AddColumn(Source, "OrderDateOnly", each DateTime.Date([OrderDateTime]), type date)
= Table.AddColumn(Prev, "OrderHour", each Time.Hour([OrderDateTime]), Int64.Type)
Then remove the original OrderDateTime column. A datetime with seconds can have millions of unique values, while date and hour columns have only thousands and 24 values, compressing much better.
38
Write a measure to calculate a closing balance that is not summed across months.
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.
39
What is the difference between FILTER and a simple boolean filter in CALCULATE?
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.
40
What is Microsoft Fabric capacity and how do capacity units work?
Fabric capacity is a pool of compute purchased as F SKUs, from F2 upward, measured in capacity units that all workloads share, including Power BI semantic models, refreshes, notebooks and pipelines. Usage is smoothed over time, so short spikes are allowed, but sustained overuse causes throttling where interactive queries are delayed or rejected. Admins use the Fabric Capacity Metrics app to monitor and plan sizing.
Frequently asked questions
What is asked in Power BI interviews for 7 years of experience?
Seven-year candidates are considered for lead, consultant or architect roles. Discussions centre on designing BI platforms on Microsoft Fabric, Direct Lake, capacity management, embedding for external users, CI/CD and data governance with sensitivity labels. Interviewers look for clear trade-off reasoning, delivery leadership and examples of measurable business impact from reporting programmes.
How many questions should I practise before a Power BI interview?
Work through all 40 questions here, then the company-wise sets for the employers you are targeting. Quality matters more than count: be able to explain why each answer is right.
Which topics matter most for 7 years of experience?
Focus first on Fabric architecture, Embedded analytics, CI/CD and governance, Delivery leadership.