.png "📊 200 Most Asked Power BI Interview Questions & Answers (2026) 🚀 Desktop + Report Server Edition | DAX, Power Query, Data Modeling, Performance Tuning & 50+ Real-World Scenarios | FreeLearning365")
🚀 200 Most Asked Power BI Interview Questions & Answers
Desktop + Report Server Edition – No Cloud, All Hands‑On
*Welcome to Tech Book & FreeLearning365 – your free, practical learning community!
At @FreeLearning365 we believe that every data pro starts with curiosity and a messy dataset. The best way to land that dream Power BI role isn’t memorising trivia – it’s building real reports, battling performance issues, and solving actual business problems. This guide gives you exactly that: 200 interview questions, including 50+ realistic, step‑by‑step scenarios, all focused on Power BI Desktop and Report Server. No fluff, no cloud – just the skills that matter on the job.* 🚀
📚 Table of Contents
Power BI Fundamentals (1–20)
Data Modeling & Star Schema (21–40)
DAX Essentials (41–70)
Advanced DAX & Time Intelligence (71–100)
Power Query & M Language (101–120)
Performance & Optimisation (121–140)
Visualisations & Report Design (141–160)
Report Server, Security & Admin (161–170)
🔥 50+ Real‑World Scenario Questions (171–220)
<a name="fund"></a>
🧩 1. Power BI Fundamentals (1–20)
1. What is Power BI?
A Microsoft business analytics tool that lets you connect to data, transform it, build interactive reports, and share insights – all within your organisation’s network.
2. Difference between Desktop, Service and Report Server?
Desktop builds reports. Service is cloud sharing. Report Server is an on‑premises portal that keeps data inside your firewall.
3. Three views in Power BI Desktop?
Report view (visuals), Data view (raw tables), Model view (relationships & schema).
4. Import vs DirectQuery vs Live Connection?
Import loads data into memory (fast, full DAX). DirectQuery queries the source live (limited DAX). Live Connection connects to an SSAS/Power BI dataset.
5. What is Query Folding?
When Power Query steps are translated into native source queries (SQL, etc.) and executed on the server, reducing load on your machine.
6. How to check if a step folds?
Right-click the step → “View Native Query”. If greyed out, it breaks folding.
7. Measure vs Calculated Column?
Calculated column stores a value per row (increases file size). Measure calculates on‑the‑fly based on filter context (no storage cost).
8. SUM vs SUMX?
SUM adds a single column. SUMX iterates a table and evaluates an expression per row (e.g., SUMX(Sales, Sales[Qty] * Sales[Price])).
9. Row context vs Filter context?
Row context = processing row‑by‑row (in calculated columns / iterators). Filter context = the set of filters from slicers/visuals that affects measures.
10. What is context transition?
When a row context is converted into an equivalent filter context, usually triggered by CALCULATE or by referencing a measure inside an iterator.
11. How does CALCULATE work?
It changes the filter context: removes existing filters (if any) on specified columns, then applies new filters, and evaluates the expression.
12. FILTER vs Boolean in CALCULATE?
Boolean (like Table[Col]="A") replaces existing filters. FILTER() keeps existing filters and adds new ones – subtle but critical difference.
13. ALL, ALLEXCEPT, ALLSELECTED?
ALL removes all filters from a column/table. ALLEXCEPT removes filters except specified columns. ALLSELECTED keeps slicer filters but ignores other visual‑level filters.
14. Why do total rows show wrong values?
Because the total recalculates the measure for the whole dataset, not summing visible rows. Common with ratios, distinct counts.
15. Fix totals with HASONEVALUE?IF(HASONEVALUE(Dim[Group]), [Measure], SUMX(VALUES(Dim[Group]), [Measure])) – tells the total to sum group results.
16. What is an expanded table?
In DAX, filtering the “one” side of a relationship automatically filters all related tables (the “many” side). This is the expanded table concept.
17. VALUES vs DISTINCT?
Both return unique values. VALUES also includes a blank row for missing matches on the many‑side; DISTINCT does not.
18. USERELATIONSHIP?
Activates an inactive relationship for a single calculation. Key for role‑playing dimensions.
19. Handling many‑to‑many relationships?
Use a bridge table of unique key combinations, with one‑to‑many relationships to both dimensions and optionally set cross‑filter direction to Both.
20. Role‑playing dimensions?
One dimension (e.g., Date) used multiple times. Create multiple relationships, keep one active, others inactive, and use USERELATIONSHIP to switch.
<a name="model"></a>
🗂️ 2. Data Modeling & Star Schema (21–40)
21. What is a Star Schema?
Fact tables in the middle, dimension tables around them. Each dimension has a one‑to‑many relationship to the fact. Simplifies DAX and boosts performance.
22. Why avoid snowflake schemas?
They force extra joins, make DAX harder, and can hurt compression. Star schema is always recommended.
23. How to create a proper Date table?
Use CALENDAR() or Power Query to generate a contiguous date range. Add columns (Year, Month, Quarter). Mark as Date Table.
24. Fiscal calendar in DAX?
Add columns like Fiscal Year = YEAR(EDATE([Date], 6)) for a July‑start fiscal year.
25. Surrogate keys?
Integer primary keys in dimensions. Small, fast for joins, and decouple from changing business keys.
26. Late‑arriving dimensions?
Create an “Unknown” row (key = -1) in the dimension and map unmatched facts to it, so no data is lost.
27. What is a degenerate dimension?
An attribute (like Invoice Number) stored in the fact table without its own dimension, because it has no descriptive columns.
28. Why use integer keys for relationships?
They compress better in VertiPaq and make joins faster than string‑based relationships.
29. What is a bridge table?
A table that resolves many‑to‑many relationships by storing valid combinations of keys from two dimensions.
30. Cross‑filter direction?
Single (default) filters from one‑side to many. Both allows filtering in both directions – use sparingly.
31. When to set cross‑filter to Both?
Only for bridge tables or when you need the many‑side to filter a dimension (e.g., “customers who bought product X”).
32. What are inactive relationships?
Relationships that exist in the model but are not used automatically. You activate them with USERELATIONSHIP.
33. How to model a “current vs historical” scenario?
Use a Type 2 slowly changing dimension (start/end dates, current flag) and filter on IsCurrent = 1.
34. Data granularity?
The level of detail in a fact table (e.g., one row per transaction line). All measures must respect this grain.
35. Best practice for dimension keys?
Use integer surrogate keys, hide them from report view, and keep business keys as attributes.
36. Why avoid many‑to‑many cardinality?
Causes ambiguity in filter propagation and often leads to incorrect totals or performance issues.
37. What is a role‑playing dimension example?
A Date table used for Order Date, Ship Date, Invoice Date. You create three relationships, only one active.
38. What is a factless fact table?
A fact table without numeric measures – used to track events (e.g., student attendance) where the count of rows is the measure.
39. How to handle multiple currencies in a model?
Add a Currency dimension and an exchange rate fact table, with effective date ranges, then use DAX to convert.
40. Why should fact tables be narrow?
Fewer columns = better compression, faster scans, lower memory. Keep descriptive attributes in dimensions.
<a name="dax"></a>
🧮 3. DAX Essentials (41–70)
41. SUM vs SUMX? (recap)
SUM = simple aggregation. SUMX = iterator, used when you need row‑by‑row calculation.
42. COUNTROWS vs DISTINCTCOUNT?
COUNTROWS counts all rows. DISTINCTCOUNT counts unique values, ignoring blanks.
43. DIVIDE function?DIVIDE(Numerator, Denominator, AlternateResult) – avoids division‑by‑zero errors.
44. COALESCE?COALESCE(Expression1, Expression2) returns the first non‑blank value. Perfect for replacing blanks with 0.
45. BLANK() vs 0?
Blank means “no data” and is ignored in averages, while 0 is a value. Often better to return BLANK() to avoid skewing metrics.
46. EARLIER function?
Refers to the outer row context inside a nested row context (used in calculated columns). Example: running total.
47. Running total with EARLIER?RunningTotal = SUMX(FILTER(Table, [Date] <= EARLIER([Date]) && [Product] = EARLIER([Product])), [Amount])
48. VAR and RETURN?
Define variables for reuse: VAR TotalSales = SUM(...) RETURN TotalSales / ... Improves performance and readability.
49. ALLSELECTED?
Removes filters coming from inside a visual, but keeps external slicer filters. Used for “% of visible total”.
50. FILTER vs ALL?
FILTER keeps existing context; ALL removes all filters from a column/table. Combine CALCULATE([Sales], ALL(Table)) to ignore filters.
51. VALUES vs ALL?
VALUES returns unique values in the current filter context. ALL returns all values regardless of context.
52. ISBLANK?
Checks if a value is blank. Use in conditional logic to replace blanks.
53. SELECTEDVALUE?SELECTEDVALUE(Column, AlternateResult) – returns the value if the column has one distinct value in the current context, else alternate.
54. HASONEVALUE?
Returns TRUE if a column has exactly one distinct value in the current context. Used for safe conditional logic.
55. CONCATENATEX?
Concatenates text values of an expression evaluated for a table, with a delimiter. Useful for creating comma‑separated lists.
56. CROSSJOIN?
Creates a Cartesian product of two tables. Used for “what‑if” or scenario analysis.
57. SUMMARIZE?
Groups a table and adds aggregations. Older function; prefer SUMMARIZECOLUMNS for queries.
58. SUMMARIZECOLUMNS?
Modern group‑by function that is faster and more predictable. Returns a table.
59. ADDCOLUMNS?
Adds calculated columns to a table expression.
60. GENERATE?
Like CROSS APPLY: for each row in Table1, evaluates Table2 and returns only rows where Table2 is non‑blank.
61. TOPN?
Returns the top N rows of a table sorted by an expression. Used inside CALCULATE.
62. RANKX?
Assigns a rank to each row of a table based on an expression. Can handle ties with Skip or Dense.
63. CALCULATETABLE?
Like CALCULATE but returns a whole table after applying filters.
64. DATESYTD / TOTALYTD?TOTALYTD([Sales], Date[Date]) – shorthand for YTD measure.
65. SAMEPERIODLASTYEAR?
Shifts dates exactly one year back. CALCULATE([Sales], SAMEPERIODLASTYEAR(Date[Date])).
66. DATEADD?
Shifts dates by a specified number of intervals (days, months, years).
67. PARALLELPERIOD?
Returns a full parallel period (e.g., previous complete month), useful for comparison.
68. DATESINPERIOD?
Returns a set of dates from a start date, going back/forward a certain number of intervals. Perfect for rolling periods.
69. TOTALMTD / TOTALQTD?
Built‑in functions for month‑to‑date and quarter‑to‑date.
70. FIRSTDATE / LASTDATE?
Return the first or last date in the current context.
<a name="advdax"></a>
🚀 4. Advanced DAX & Time Intelligence (71–100)
71. Moving average (3‑month)?AVERAGEX(DATESINPERIOD(Date[Date], LASTDATE(Date[Date]), -3, MONTH), [Sales])
72. Prior YTD?CALCULATE([Sales], SAMEPERIODLASTYEAR(DATESYTD(Date[Date])))
73. Year‑over‑Year Growth %?DIVIDE([Sales] - CALCULATE([Sales], SAMEPERIODLASTYEAR(Date[Date])), CALCULATE([Sales], SAMEPERIODLASTYEAR(Date[Date])))
74. How to handle non‑standard calendars (4‑4‑5)?
Use a custom date table with pre‑calculated period columns, then DAX aggregates over those columns.
75. Calculation Groups?
Define a set of time intelligence transformations (YTD, PY, YoY%) that can be applied to any measure. Created with Tabular Editor.
76. SELECTEDMEASURE?
In calculation groups, references the measure currently being evaluated. Enables writing one formula for all measures.
77. Field Parameters?
A table of measures or dimensions that users can select via a slicer to dynamically change what a visual shows.
78. What‑If parameters?
A disconnected table of numbers (e.g., discount 0‑50%) and a measure that captures the selected value for scenario modelling.
79. Disconnected table?
A table with no relationships. Used for parameter selection, dynamic top N, and custom grouping.
80. TREATAS?
Applies a virtual filter from a table expression to columns of another table without requiring a physical relationship.
81. GENERATE vs CROSSJOIN?
GENERATE works like CROSS APPLY; CROSSJOIN is a full Cartesian product.
82. SUMMARIZECOLUMNS vs SUMMARIZE?
SUMMARIZECOLUMNS is the modern, faster grouping function; SUMMARIZE can cause unexpected filter behavior.
83. Dynamic title?
Create a measure like "Sales Report " & SELECTEDVALUE(Year) and bind it to the visual’s title using conditional formatting.
84. Dynamic measure selection?
Use a disconnected table with measure names and a SWITCH measure that returns the selected measure.
85. SWITCH in DAX?SWITCH(TRUE(), condition1, result1, condition2, result2, else_result) – cleaner than nested IFs.
86. ISFILTERED?
Checks whether a column is directly filtered (via slicer or filter pane). Useful for dynamic behaviour.
87. ISCROSSFILTERED?
Checks if a column has a cross‑filter from another table.
88. KEEPFILTERS?
Used inside CALCULATE to preserve existing filters and add new ones, rather than overwriting.
89. USERELATIONSHIP inside CALCULATE?CALCULATE([Sales], USERELATIONSHIP(Sales[ShipDateKey], Date[DateKey])) – activates an inactive relationship.
90. ALLEXCEPT?
Removes all filters except on specified columns. CALCULATE([Sales], ALLEXCEPT(Sales, Sales[Product])) gives product‑share of total.
91. RANKX with ties?
Use RANKX(ALL(Table[Product]), [Sales], , DESC, Dense) to handle ties without skipping numbers.
92. TOPN inside CALCULATE?CALCULATE([Sales], TOPN(5, Products, [Sales])) returns sales for top 5 products.
93. DAX variables performance?
Variables are evaluated once and reused, reducing multiple storage engine calls. Critical for complex measures.
94. Why does CALCULATE change filter context?
It first removes filters on the columns listed as arguments, then applies the new filters. That’s why CALCULATE([Sales], Table[Col]=“A”) overrides existing selections.
95. Use FILTER with ALL to ignore slicers?CALCULATE([Sales], FILTER(ALL(Table), Table[Col] = “A”)) – ignores any external filters on that table.
96. How to get the value of a previous row?
In DAX, use OFFSET or WINDOW (new window functions) or EARLIER in calculated columns.
97. OFFSE, INDEX, WINDOW?
New DAX window functions for advanced relative calculations (requires latest Desktop version).
98. NATURALINNERJOIN?
Joins two tables on columns with the same name and data lineage. Limited to tables from the same source.
99. Handling large strings in DAX?
DAX stores text as 1‑byte characters; limit string columns in facts to improve compression.
100. Best way to learn DAX?
Build star schemas, practice filter context with simple examples, and use DAX Studio to see query plans.
<a name="pq"></a>
⚙️ 5. Power Query & M Language (101–120)
101. What is Power Query?
The data transformation engine in Power BI for cleaning, shaping, and combining data before loading.
102. M language?
The functional programming language behind Power Query steps.
103. Merge vs Append?
Merge = join tables side‑by‑side. Append = stack tables top‑to‑bottom (union).
104. Merge join kinds?
Left Outer (all from first), Right Outer, Full Outer, Inner, Left Anti, Right Anti.
105. Unpivot?
Transforms columns into rows. Essential for normalizing crosstab data.
106. Pivot?
Converts rows into columns. Opposite of Unpivot.
107. How to handle errors in Power Query?
Use try ... otherwise ... or the “Replace Errors” UI.
108. Table.Buffer?
Loads a table into memory to prevent repeated source calls and stabilise sorting. Use only when needed.
109. List.Generate?
Builds a list iteratively (e.g., date sequence). Useful for generating custom series.
110. Function parameter?
A variable (like file path) that can be referenced in queries. Makes reports dynamic.
111. Dynamic file source?
Create a parameter for the file path; use it in the Source step. Update parameter to refresh data from a new location.
112. Combine files from a folder?
Connect to folder, click “Combine files”. Power Query creates a sample query and applies it to all files.
113. Excel.Workbook vs Csv.Document?
Excel.Workbook reads .xlsx files; Csv.Document parses raw CSV text with delimiter settings.
114. How to remove duplicate rows in Power Query?
Select columns → Remove Duplicates (uses the first occurrence).
115. Fill Down / Fill Up?
Copies the value from the nearest non‑null row downward/upward. Perfect for cleaning grouped headers.
116. Group By in Power Query?
Similar to SQL GROUP BY. Select columns, add aggregations, and output as summary table.
117. Conditional column?
Adds a new column based on IF/ELSE logic (like a CASE statement).
118. How to convert column data types?
Right‑click column → Change Type. Always set types early to catch errors.
119. Remove columns vs Choose Columns?
Remove Columns deletes specific columns; Choose Columns keeps only selected ones.
120. How to optimise Power Query?
Fold queries, filter early, remove unused columns, avoid expensive functions like buffering unless necessary.
<a name="perf"></a>
⚡ 6. Performance & Optimisation (121–140)
121. What is VertiPaq?
The columnar in‑memory engine that compresses and stores data for fast DAX queries.
122. How does VertiPaq compression work?
Dictionary encoding, run‑length encoding, and value encoding on sorted column values.
123. Cardinality effect on model size?
High cardinality (many unique values) = larger dictionary, slower compression, more memory.
124. How to reduce model size?
Remove unused columns, use integer keys, disable auto‑date/time, split datetime, avoid high‑cardinality strings in facts.
125. What is a thin fact table?
Only numeric measures and integer keys. All strings go in dimensions.
126. How to analyse DAX performance?
Use Performance Analyzer in Desktop, copy query, run in DAX Studio to see server timings and query plan.
127. What are SE (Storage Engine) and FE (Formula Engine)?
SE handles retrieving data, FE evaluates complex logic. Aim for more work in SE (faster).
128. What is CallbackDataID?
Indicates the FE is asking the SE for rows one‑by‑one. Too many callbacks = slow performance.
129. How to avoid CallbackDataID?
Use simpler measures, avoid FILTER with complex tables, prefer SUMMARIZECOLUMNS.
130. Why do bidirectional relationships slow performance?
They increase filter complexity and often lead to many‑to‑many filter propagation, causing the engine to do extra work.
131. How to improve DirectQuery performance?
Ensure source has indexes, use aggregations tables, limit visuals to small result sets.
132. What are aggregations?
Pre‑aggregated import tables (daily sums) that Power BI can use instead of querying the detail table live.
133. Composite models?
Mix Import and DirectQuery in the same model. Use DirectQuery for real‑time facts, Import for dimensions.
134. How to identify slow queries?
Performance Analyzer → Copy Query → DAX Studio → Server Timings. Look for high FE time, many callbacks.
135. Auto‑date/time issues?
Creates hidden date tables for every date column, bloating the model. Always disable in Options and use a custom date table.
136. Best practice for data types?
Use Date instead of DateTime when time is irrelevant, use Whole Number where possible, avoid Text in facts.
137. IsAvailableInMDX property?
Hides columns/measures from MDX clients (like Excel) to keep the field list clean.
138. How to test RLS performance impact?
Run queries as a user with applied roles using DAX Studio’s “Run As” feature, check for plan changes.
139. What is query folding and why important?
Push transformations to source, reduce data brought into Power Query, speed up refresh. Break folding = slower refresh.
140. How to optimise Power Query for large datasets?
Filter rows early, remove unused columns before merges, use foldable steps, buffer only if necessary.
<a name="viz"></a>
📊 7. Visualisations & Report Design (141–160)
141. Report layout best practices?
Place key KPIs top‑left, use consistent colours, adequate white space, readable fonts, enable drill‑through for details.
142. Custom tooltips?
Create a small report page (Tooltip size), enable “Allow use as tooltip”, assign it to a visual’s tooltip property.
143. Bookmark vs Button?
Bookmark saves the state of a page (filters, slicers, visual focus). Button triggers an action (bookmark, navigation, Q&A).
144. Drillthrough?
Right‑click a data point → Drillthrough to a detail page. The target page uses drillthrough filters.
145. How to create a dynamic title?
Build a DAX measure that concatenates text with SELECTEDVALUE, then use conditional formatting to apply it to the title.
146. What are KPI visuals?
Shows a single value with target and trend axis. Bind a base measure, target measure, and trend axis.
147. Slicer vs Filter pane?
Slicers are on‑canvas interactive filters. Filter pane is a built‑in panel. Slicers offer more formatting and sync options.
148. Sync slicers?
A feature that makes a slicer appear and work across multiple pages. Useful for consistent filtering.
149. Buttons to switch pages?
Add a button, set action type to “Page navigation”, choose destination page. Gives app‑like feel.
150. How to make a report mobile‑friendly?
Design using Phone layout view, use simple card visuals, and avoid complex tables.
151. What is a decomposition tree?
AI‑powered visual that lets users break down a measure by multiple dimensions in an ad‑hoc way.
152. Q&A visual?
Users type natural language questions and get automatic visuals. Requires well‑named tables/columns.
153. How to use themes?
Import a JSON theme file via View → Themes. Changes the colour palette and formatting for the whole report.
154. Custom visuals from AppSource?
Download from AppSource or file, import via … → Get more visuals. Ensure compatibility with Report Server.
155. Shape map?
A map visual using custom GeoJSON files (great for on‑prem without internet maps).
156. Conditional formatting in tables?
Right‑click a field in the Values well → Conditional formatting → Background colour, font colour, icons, etc.
157. How to align visuals precisely?
Use “Format” pane → General → X, Y coordinates, and width/height for pixel‑perfect alignment.
158. What is the “Selection” pane?
Shows all objects on the page; hide, lock, or reorder layers.
159. How to create a reset button for slicers?
Use a bookmark that captures the default state of slicers, then assign it to a button.
160. Best practice for accessibility?
Use high‑contrast colours, meaningful alt text, and a logical tab order. Check with the Accessibility checker.
<a name="admin"></a>
🔐 8. Report Server, Security & Admin (161–170)
161. How to publish to Report Server?
Use Power BI Desktop (Report Server version) → File → Save As → Power BI Report Server. Upload via web portal.
162. Schedule refresh on Report Server?
In the web portal, manage the report → Data Sources → store credentials → Schedule Refresh.
163. Row‑Level Security (RLS) setup?
In Desktop, create a role with a DAX filter (e.g., [UserEmail] = USERPRINCIPALNAME()). Publish, then assign domain users to the role on Report Server.
164. USERNAME() vs USERPRINCIPALNAME()?
USERNAME() returns domain\user; USERPRINCIPALNAME() returns user@domain (email format). Both work for on‑prem RLS.
165. Dynamic RLS with mapping table?
Maintain a table (User, Region) and filter the fact table via relationships so users see only their regions.
166. Test RLS in Desktop?
Modelling → View As → select a role and optionally enter a username to see filtered data.
167. How to hide reports from certain users?
Use folder permissions on the Report Server portal. Users see only folders/reports they have access to.
168. How to embed a Report Server report in a web app?
Use an iframe with ?rs:embed=true and enable Windows Authentication.
169. Performance impact of large log files on Report Server?
The report server database can grow; configure a retention policy and regularly clean execution log tables.
170. How to handle Report Server version compatibility?
Use the exact matching version of Power BI Desktop (Report Server edition) provided on the server’s download page.
<a name="scenario"></a>
🔥 9. 50+ Real‑World Scenario Questions (171–220)
(Each answer includes a practical, step‑by‑step solution.)
171. Scenario: Sales table has OrderDate and ShipDate, both need to filter by a single Date slicer.
Create two relationships between Sales and Date (OrderDate → active, ShipDate → inactive). For ship date analysis, create a measure using USERELATIONSHIP: CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDateKey], Date[DateKey])). Use the active relationship for order date by default, and switch via measure for ship date.
172. Scenario: Users want a “Top N Customers” report with a slicer to choose N.
Create a disconnected table with numbers 1–20. Add a slicer. Measure: SelectedN = SELECTEDVALUE(NTable[Number], 5). Then another measure to calculate sales for top N using RANKX and FILTER: CALCULATE([TotalSales], FILTER(Values(Customer[CustomerID]), RANKX(ALL(Customer), [TotalSales]) <= [SelectedN])).
173. Scenario: Report loads too slowly on Report Server; refresh takes 30 minutes.
Analyse: turn off auto‑date/time, remove high‑cardinality columns from fact, split datetime columns. Use a stored procedure for source data that filters and aggregates before Power Query. Implement incremental refresh if using premium; on Report Server, reduce overall data by filtering to last 3 years. After optimisation, refresh dropped to 5 minutes.
174. Scenario: Finance needs a P&L report where measures are on rows (dynamic), not columns.
Create a dimension table “P&L Lines” with rows like Revenue, COGS, Gross Margin, etc. Write a measure P&L Value = SWITCH(TRUE(), VALUES('P&L'[Line]) = "Revenue", [Revenue], ...). Place ‘P&L’[Line] on rows of a matrix and the measure in values.
175. Scenario: How to handle “Budget vs Actual” when budget is at month level and actuals are daily?
Build a star schema: a date dimension at day level, a fact table for actuals (day grain), and a budget fact table at month grain. Use a measure Budget MTD = TOTALMTD(SUM(Budget[Amount]), Date[Date]) and Actual MTD = TOTALMTD(SUM(Actuals[Amount]), Date[Date]). The date dimension bridges both grains automatically.
176. Scenario: You must create a report that exports to Excel exactly as shown, with merged cells.
Power BI doesn’t natively merge cells. Instead, build a paginated report (Report Builder) on the same dataset and publish to Report Server. Paginated reports support exact layout, including merged cells.
177. Scenario: A measure returns blank, but you want to show “No Sales” in a card.
Measure = IF(ISBLANK([SalesMeasure]), "No Sales", FORMAT([SalesMeasure], "#,##0")). However, this changes data type to text, so use a separate measure for display cards only.
178. Scenario: Duplicate rows in fact after merging two tables in Power Query.
Use “Remove Duplicates” after merge, or ensure merge keys are unique. Better: build proper star schema with unique dimension keys.
179. Scenario: Data source changes column names frequently. How to build a resilient query?
In Power Query, use column positions (Table.ColumnNames) or promote headers and rename with a custom function that maps expected names. Or use a parameterised mapping table.
180. Scenario: Need to compare this week’s sales to the same week last year (ISO week).
Add ISO week and year columns to your date table. DAX: CALCULATE([Sales], FILTER(ALL(Date), Date[ISOYear] = SELECTEDVALUE(Date[ISOYear])-1 && Date[ISOWeek] = SELECTEDVALUE(Date[ISOWeek]))).
181. Scenario: A visual should show YoY% but also colour red for negative growth, green for positive.
Create a measure for YoY%. For formatting, go to Conditional formatting on the visual, choose “Background Color” or “Font Color”, format by rules: if YoY% < 0 red, >0 green.
182. Scenario: How to create a rolling 12‑month forecast starting from the last actual month?
In Power Query, generate a list of future months. Append actuals and forecast. Use a measure that takes actuals where date <= today, forecast otherwise. Use a parameter for growth rate.
183. Scenario: You need a report that hides certain columns for specific users (Object‑Level Security).
On Report Server, OLS is not available. Workaround: create two versions of the report with different columns, and use folder permissions to show the appropriate version to users.
184. Scenario: A fact table has 500 million rows, and you only need summaries by month and region.
In Power Query, group by Month and Region, aggregate the measures, and load only the aggregated table. This can reduce rows to a few thousand.
185. Scenario: User complains that the “Export to CSV” from a table visual includes hidden columns.
In the table visual, only the fields placed in the Values well are exported. Use the Selection pane to hide visual elements, but ensure only desired fields are in the visual.
186. Scenario: How to display “% of Parent Row” in a matrix.
Use DAX measure: Percent of Parent = DIVIDE([Sales], CALCULATE([Sales], ALLSELECTED(Dim[SubCategory]))) assuming SubCategory is the child group.
187. Scenario: A measure works in a card but shows error in a table visual.
Likely a filter context issue – the measure expects a single value but receives multiple. Add error handling with IF(HASONEVALUE(...), measure, BLANK()).
188. Scenario: Data source is a shared Excel file that others keep renaming columns.
Use Power Query to reference columns by index rather than name, or create a custom function that renames based on a “crosswalk” table. Even better: use a database instead of Excel.
189. Scenario: Build a subscription‑style report that emails a PDF of the report on Report Server.
Use a data‑driven subscription (via SQL Server Agent) with a report that accepts parameters. Schedule it to run daily and email the PDF.
190. Scenario: Two fact tables (Sales, Returns) need to be combined into one visual with a common date.
Create a star schema with a shared Date dimension. Write separate measures for Sales and Returns, then put both measures in a single chart using “Show secondary axis” if scales differ.
191. Scenario: RLS: regional managers should see only their region, but executives see all.
Create a mapping table (User, Region) and a relationship to Sales[Region]. RLS role filter: = [User] = USERNAME(). Assign users to role. For executives, either add them to all regions in mapping or create a separate role without filter.
192. Scenario: The requirement is to show “New Customers” (first purchase in the selected period) count.
Measure: New Customers = COUNTROWS(FILTER(VALUES(Sales[CustomerID]), CALCULATE(MIN(Sales[Date]), ALL(Date)) >= MIN(Date[Date]) && CALCULATE(MIN(Sales[Date]), ALL(Date)) <= MAX(Date[Date]))). This checks if the customer’s first purchase falls within the current date range.
193. Scenario: Create a chart that shows actuals up to today and budget for the full month.
Measures: Actual YTD = CALCULATE([Sales], FILTER(Date, Date[Date] <= TODAY())), Budget = SUM(Budget[Amount]). Combine in a combo chart: Actual as column, Budget as line.
194. Scenario: Need to convert a flat file with monthly columns into a proper date table.
In Power Query, select all month columns → Unpivot. Parse the column names to get a date column, then load.
195. Scenario: Report must open with specific slicers pre‑selected (e.g., current month).
Use a bookmark that captures the default state, or use DAX in a calculated table for default selections. For a simpler approach, set the slicer in Desktop and save; the last saved state is retained.
196. Scenario: Users need to switch between “Weekly” and “Monthly” aggregation dynamically.
Create a disconnected table “Periodicity” (Weekly, Monthly). Measure: Selected Period = SELECTEDVALUE(Periodicity). Then create a measure that groups date accordingly. Alternatively, use Field Parameters and two different measures.
197. Scenario: The data model has 20 relationships, but a query for a simple table visual is slow.
Check if relationships are causing circular filter chains or many‑to‑many ambiguity. Simplify to star schema; use DAX Studio to see which relationships are used. Removing unnecessary relationships often resolves.
198. Scenario: A report shows duplicate customer names because the source has slight variations.
Use Power Query to clean: trim, uppercase, create a mapping table of variations to canonical names, then merge. Implement a “Customer Master” dimension.
199. Scenario: Need to show a percentage that sums to 100% across all rows, ignoring slicers for some dimensions.% of All Products = DIVIDE([Sales], CALCULATE([Sales], ALL(Products))). This ignores product filter but respects other slicers (like date, region) because ALL only removes product filter.
200. Scenario: Executive dashboard: single page with key KPIs, click a KPI to drill to detailed reports.
Use buttons with action type “Page navigation” for each KPI. Place transparent shapes over KPI cards linked to bookmarks or pages. Use drillthrough for detailed analysis from charts.
201. Scenario: How to handle data that arrives hourly, while a report must be accurate to the last refresh.
On Report Server, schedule refresh as frequently as allowed (max once every 15 minutes with Power BI RS, but you can orchestrate via SQL Agent to run a PowerShell script that triggers refresh). Or use DirectQuery for real‑time, but with performance caution.
202. Scenario: Create a “ragged hierarchy” (e.g., employee → manager with varying depth).
Build a path column in your dimension (e.g., ManagerPath) and use PATH functions or flatten in Power Query. In Power BI, use a matrix with drill‑down enabled and a column with the hierarchy levels.
203. Scenario: Users want to see “Year to Date” but also compare with previous year YTD on the same chart.
Measures: Sales YTD = TOTALYTD([Sales], Date[Date]), Sales PYTD = CALCULATE([Sales YTD], SAMEPERIODLASTYEAR(Date[Date])). Plot both on a line chart with Date[Date] on axis.
204. Scenario: A measure is computing slow because of nested iterators.
Rewrite using variables and SUMMARIZECOLUMNS. For example, instead of SUMX(Table, [Qty] * RELATED(Product[Price])), pre‑calculate in Power Query or use a simpler aggregation.
205. Scenario: A Power BI report must be printed and look exactly like a PDF form.
Build a paginated report (Report Builder) on top of the same data model, then publish to Report Server. Paginated reports are pixel‑perfect for printing.
206. Scenario: The requirement is to hide a whole page for certain users.
Not directly possible on Report Server. Workaround: create two reports (one with, one without the page) and assign folder permissions.
207. Scenario: A date dimension goes from 2010 to 2030, but model is large due to many future dates.
In Power Query, filter the date table to the minimum required range (e.g., 2015‑2025) before loading. This reduces memory.
208. Scenario: Need to show “Sales per sq. ft.” but area data is in a separate lookup table with historic changes.
Use a Type 2 SCD for Area, connect to sales via store key and date range. DAX: DIVIDE(SUM(Sales[Amount]), SUM(Area[SquareFeet])) with appropriate filter context on effective date.
209. Scenario: A user selects “All” in a slicer and a card shows “(Multiple)” instead of a value.
Use SELECTEDVALUE(column, "All Selected") in your display measure. This avoids showing “(Blank)”.
210. Scenario: Report Server performance is slow when many users access reports simultaneously.
Check server resources (RAM, CPU). Move data refreshes to off‑peak hours, optimise data models, and consider caching or pre‑loading reports via subscriptions.
211. Scenario: You inherit a Power BI file with 100+ measures, all defined without variables.
Refactor critical measures using DAX Studio to identify heavy ones, then rewrite with VAR to improve readability and performance. Document with measure descriptions.
212. Scenario: Need to compare actual sales to a target that is set at the quarter level.
Create a Target fact table at quarter grain, relate to Date table. Measure: Target = SUM(Target[Target]). Then YTD target: TOTALYTD([Target], Date[Date]). Actual vs Target in a combo chart.
213. Scenario: CSV file columns shift order every month; import fails.
In Power Query, don’t promote headers automatically. Instead, use a step that sets column names explicitly by position, or use a custom function that renames columns based on a fixed mapping table.
214. Scenario: Need to anonymise data for a demo (mask names, email).
In Power Query, use replace values or a custom column that extracts first character and concatenates with “****”. Also, remove sensitive columns entirely before loading.
215. Scenario: A report must show the last refresh date and time for users.
Create a measure Last Refresh = "Last updated: " & FORMAT(NOW(), "yyyy-mm-dd hh:nn:ss"). Place it in a card. Note: this shows report time, not data refresh time. For data refresh, store the refresh timestamp in a table.
216. Scenario: Sum of revenue by product does not match the total in source system.
Check for duplicate or missing relationships, ensure direction is correct, and verify that no table has many‑to‑many ambiguity. Use DAX Studio to trace the exact filter context of a cell.
217. Scenario: A user asks for a “pause/play” button that cycles through months.
Create a play axis using a calculated table of month numbers, use a slider or button to increment a selected value, then filter the page to that month. Use bookmarks for button states.
218. Scenario: Power Query refresh fails because of a password‑protected Excel source.
Store credentials within the data source settings (Windows or basic). If using a parameter for file path, ensure credentials are set at the folder level.
219. Scenario: Users want to input comments directly into a Power BI report and save them back to a database.
Power BI alone cannot write back. Use Power Apps visual to allow user input and store in a SharePoint list or SQL table, then reflect in the report.
220. Scenario: Final expert challenge: build a complete sales analysis solution for a 50‑user company, entirely on‑prem.
Use star schema with SQL Server as source; Power Query for ETL; import mode for fast queries; RLS for regional managers; paginated reports for operational printing; Power BI Desktop RS for authoring; scheduled refresh via Report Server; Excel reports via Analyze in Excel (live connection). Result: a scalable, secure, high‑performance BI platform.
✨ Final Words from @FreeLearning365
Power BI is more than a tool – it’s your ability to turn messy data into clear, actionable stories. Every question in this guide came from real interviews and real projects. Master the star schema, deeply understand CALCULATE, and practise with messy datasets – that’s how you become the developer everyone wants on their team.
If this guide helped you, share it with your network, tag @FreeLearning365 and @techbook24, and keep building. Your next career leap is just one report away. 🚀
#PowerBI #DAX #InterviewQuestions #PowerBIReportServer #DataAnalytics #FreeLearning365 #TechBook
.png)
.png&description=📊 200 Most Asked Power BI Interview Questions & Answers (2026) 🚀 Desktop + Report Server Edition | DAX, Power Query, Data Modeling, Performance Tuning & 50+ Real-World Scenarios | FreeLearning365){kind=link}
0 Comments
thanks for your comments!