Power BI Technical Round Questions &amp; Answers

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 are bookmarks and how are they used?

Visualisation and Report DesignMedium

Show answer

Bookmarks capture the current state of a report page, including filters, slicers, visual visibility and sort order. They are used for storytelling, toggling between chart and table views with buttons, creating custom navigation, and resetting filters. Combined with the selection pane, they show and hide visuals. Too many bookmarks become hard to maintain, so field parameters are often a simpler alternative for switching measures.

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

Show answer

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.

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.

Sales data has region codes, while targets use region names. How do you combine them?

Show answer

I would create or import a region mapping table with code and name, clean both sides by trimming spaces and fixing case, then merge in Power Query or better, build a Region dimension table with both columns and relate sales by code and targets by name to it. Unmatched rows should be checked using a left anti join so missing mappings are fixed rather than silently dropped.

How do you handle errors in Power Query without failing the whole refresh?

Show answer

Use Keep Errors on a copy of the query to inspect the bad rows, then fix the cause, such as wrong data types or invalid dates. Replace Errors can substitute a value, and in M try ... otherwise catches errors per value: = Table.AddColumn(Source, "AmountNum", each try Number.From([Amount]) otherwise null, type nullable number) Log error rows to a separate query so they can be reviewed rather than silently dropped.

What does the Best Practice Analyzer do?

Power BI Service, Sharing and SecurityEasy

Show answer

Best Practice Analyzer, in Tabular Editor and also available in Fabric notebooks through semantic link labs, scans a semantic model against a set of rules, such as avoiding bi-directional relationships, hiding foreign keys, not summarising numeric IDs, using DIVIDE, and setting format strings. It lists violations with their severity so teams can fix them before release. Rules can be customised to match team standards.

How do you set up scheduled refresh in the Power BI Service?

Show answer

Open the workspace, select the semantic model's settings, and check Data source credentials and Gateway connection, mapping on-premises sources to a gateway. Then turn on Scheduled refresh, choose time zone, frequency and time slots, and add failure notification contacts. Pro allows up to 8 refreshes per day and Premium or Fabric capacity up to 48. Stagger refresh times to avoid source load peaks.

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.

How do you set up an app with different audiences?

Show answer

In the workspace choose Create app or Update app, add the reports and dashboards, then in the Audience tab create audiences such as Sales Managers and Finance, choose which content each audience sees and grant access to the right Entra ID security groups. Publish the app and share the link. Remember RLS still controls rows inside reports; audiences only control which items are visible.

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

Power BI Service, Sharing and SecurityHard

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.

What does the XMLA endpoint allow you to do?

Show answer

The XMLA endpoint exposes semantic models on Premium, PPU or Fabric capacity like an Analysis Services server. Tools such as Tabular Editor, DAX Studio, SQL Server Management Studio and ALM Toolkit can then read or write the model, manage partitions, refresh specific tables, script deployments and apply object-level security. Read-write mode must be enabled in capacity settings by an admin.

Your workspace has grown to 150 items and nobody knows which reports are used. How do you clean it up?

Show answer

I would export usage from the activity log or the workspace usage metrics, list items not viewed in 90 days and confirm with owners before archiving. Duplicate models are consolidated into shared semantic models, naming standards are applied, and workspaces are split by domain with clear owners. A quarterly review keeps it tidy. This also reduces refresh load on capacity.

How do you set up a custom function in Power Query to apply logic to many files?

Power Query and Data TransformationHard

Show answer

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.

What is a dataflow and how is it different from Power Query in Desktop?

Show answer

A dataflow runs Power Query transformations in the Power BI Service and stores the result in the cloud, so multiple semantic models can reuse the same cleaned tables. Desktop Power Query logic lives inside one model only. Dataflows Gen2 in Fabric can also write output to lakehouses or warehouses. Dataflows centralise preparation logic, reduce duplicate source load and separate data prep from modelling.

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 Power BI and what are its main components?

Power BI FundamentalsEasy

Show answer

Power BI is a business intelligence platform for connecting to data, modelling it and building interactive reports and dashboards. Main components are Power BI Desktop for building reports, Power BI Service (part of Microsoft Fabric) for publishing, sharing and refresh, Power BI Mobile apps for viewing, the on-premises data gateway for refreshing local sources, and Power BI Report Server for fully on-premises hosting of paginated and Power BI reports.

What are paginated reports and when would you use them?

Power BI FundamentalsMedium

Show answer

Paginated reports, built with Power BI Report Builder, are pixel-perfect, print-ready reports that can span many pages, such as invoices, statements or regulatory reports with long tables. They are ideal when users need exact layout, export to PDF or Excel, or detailed row-level listings. Interactive Power BI reports are better for exploration. Paginated reports can run in the Service with Pro, PPU or Fabric capacity.

What is query folding and why does it matter?

Show answer

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.

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.

How do you create a fiscal year column starting in April?

Show answer

Fiscal Year = VAR m = MONTH('Date'[Date]) VAR y = YEAR('Date'[Date]) RETURN "FY " & IF(m >= 4, y, y - 1) & "-" & RIGHT(IF(m >= 4, y + 1, y), 2) For April 2026 this returns FY 2026-27, which matches the Indian financial year. Add a fiscal month number column for sorting too.

What tools do you use to analyse Power BI performance?

Show answer

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.

Describe how you would review another developer's Power BI model before release.

Show answer

I check the model diagram for a clean star schema and single-direction relationships, run Best Practice Analyzer, and review VertiPaq Analyzer for oversized columns. I read key measures for correctness and naming, check that keys are hidden and formats set, test RLS roles, and run Performance Analyzer on main pages. I share findings as a short prioritised list, explaining why each change matters.

Write a running total measure.

Show answer

Running Total = VAR maxDate = MAX('Date'[Date]) RETURN CALCULATE( [Total Sales], 'Date'[Date] <= maxDate, REMOVEFILTERS('Date') ) REMOVEFILTERS clears the current period filter so all earlier dates are included. In a visual-only scenario, the visual calculation RUNNINGSUM is simpler.

What is the difference between SAMEPERIODLASTYEAR, DATEADD and PARALLELPERIOD?

Performance Optimisation and AdministrationEasy

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.

A stakeholder insists on a 3D pie chart with 12 slices. How do you handle it?

Visualisation and Report DesignMedium

Show answer

I would understand what they want to see, usually share of total by category, then show a quick comparison: a sorted bar chart beside the pie, explaining that bars make small differences readable while 3D distorts sizes. Offering a donut with top five plus Others is a compromise. Usually seeing both side by side convinces them, and the final choice remains theirs.

Why can too many visuals on a page slow down a report?

Show answer

Each visual sends at least one DAX query when the page loads or when filters change, and visuals render in the browser. A page with 30 visuals, especially complex tables and custom visuals, sends many queries in parallel, competing for capacity and causing long load times. Reducing visuals, combining cards into one multi-row card or using tooltips and drill-through pages improves speed.

What is the difference between drill-down and drill-through?

Visualisation and Report DesignEasy

Show answer

Drill-down moves within the same visual through a hierarchy, such as Year to Quarter to Month, using the drill buttons. Drill-through moves the user to a separate detail page filtered by the selected item, such as right-clicking a customer to open a Customer Details page. Drill-through pages are set up by adding fields to the drill-through well and include a back button.

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

DAX FundamentalsMedium

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]).

Rewrite a slow measure to use variables and avoid repeated calculation.

Show answer

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.

Write a measure that counts new customers in a period.

Advanced DAX and Time IntelligenceHard

Show answer

New Customers = VAR minDate = MIN('Date'[Date]) RETURN COUNTROWS( FILTER( VALUES(Sales[CustomerID]), CALCULATE(MIN(Sales[OrderDate]), REMOVEFILTERS('Date')) >= minDate ) ) A customer is new if their first ever order falls in the current period.

What are deployment pipelines?

Performance Optimisation and AdministrationHard

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 Microsoft Fabric capacity and how do capacity units work?

Show answer

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.

How do you create a dynamic axis using field parameters?

Visualisation and Report DesignMedium

Show answer

Go to Modeling, New parameter, Fields, and add the fields such as Region, Category and Channel. Power BI creates a parameter table and slicer. Put the parameter field on the axis of a chart; users select which dimension to show from the slicer. You can add measures to a separate field parameter so users switch both the measure and the axis.

How do you use Git integration with a Power BI workspace?

Power BI FundamentalsHard

Show answer

Save the report as a Power BI Project (PBIP), commit the folder to a Git repository in Azure DevOps or GitHub, and connect a Fabric workspace to a branch through Workspace settings, Git integration. Changes can then be committed from the workspace or synced from the repository. Developers work on feature branches and merge through pull requests, giving history, reviews and rollback for reports and models.

What is Direct Lake storage mode?

Power BI FundamentalsHard

Show answer

Direct Lake is a storage mode for semantic models on Fabric capacity that reads Delta Parquet files from OneLake straight into the VertiPaq engine on demand. It avoids scheduled import refreshes and the latency of DirectQuery, so reports show fresh data with near-import performance. If a query exceeds capacity guardrails or uses unsupported features, it can fall back to DirectQuery, which is slower.

A manager must see their whole reporting hierarchy, not just their own team. How do you build RLS for that?

Power BI Service, Sharing and SecurityHard

Show answer

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.

What is the difference between ALL, ALLEXCEPT and REMOVEFILTERS?

DAX FundamentalsMedium

Show answer

ALL returns all rows of a table or column ignoring filters, and as a CALCULATE argument it removes filters, useful for percent of total. REMOVEFILTERS does the same removal but only as a CALCULATE modifier and reads more clearly. ALLEXCEPT removes all filters from a table except the listed columns, such as keeping the Year filter while removing others. ALLSELECTED keeps filters from outside the visual, like slicers.

How does the VertiPaq engine store and compress data?

Show answer

VertiPaq is the in-memory columnar engine for Import models. It stores each column separately and compresses it using dictionary encoding, which maps unique values to integers, value encoding for numbers, and run-length encoding for repeated sequences. Compression depends mostly on column cardinality, so columns with few unique values compress very well, while unique IDs and precise timestamps take most of the memory.

Split a datetime column to reduce cardinality in Power Query.