Power BI Interview Questions &amp; Answers

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 is the difference between a report and a dashboard in Power BI?

Show answer

A report is a multi-page, fully interactive document built on one semantic model, with slicers, filters, drill-through and cross-highlighting. A dashboard is a single-page canvas in the Power BI Service made of pinned tiles that can come from several reports and models; it is used for a quick at-a-glance view. Dashboards are created only in the Service, while reports are usually built in Desktop.

Explain the typical Power BI workflow from data to insight.

Show answer

First connect to sources such as Excel, SQL Server or SharePoint and clean the data in Power Query. Then build a data model with relationships, ideally a star schema, and write DAX measures. Next design report visuals and interactions in Desktop. Publish to a workspace in the Power BI Service, set scheduled refresh through a gateway if needed, apply row-level security and share through an app or dashboard.

What is a semantic model in Power BI?

Show answer

A semantic model, earlier called a dataset, is the published data model behind reports: tables, relationships, measures, hierarchies, security roles and refresh settings. Many reports can connect to one shared semantic model, which keeps business logic and numbers consistent across teams. Certified or promoted endorsements help users find trusted models. Build permission controls who can create new reports on it.

What is the difference between Power BI and Excel for reporting?

Show answer

Excel is flexible for ad hoc analysis and small models but struggles with large data, version control and secure sharing. Power BI handles millions of rows with a compressed columnar engine, central semantic models, scheduled refresh, row-level security and interactive web and mobile sharing. They work together: Power Query and Power Pivot exist in both, and users can analyse Power BI models in Excel.

What is a Power BI workspace and what roles does it have?

Show answer

A workspace is a container in the Power BI Service for semantic models, reports, dashboards, dataflows and other items that a team works on together. Roles are Admin, Member, Contributor and Viewer. Admins manage everything including access, Members can publish and share, Contributors can create and edit content but not share, and Viewers can only view. Consumers are usually given access through an app instead.

A manager wants a single KPI view combining sales, HR and finance reports. What do you build?

Show answer

I would build a dashboard in the Power BI Service and pin the key KPI tiles from each report, so the manager sees all areas on one page and can click through to details. If the KPIs need cross-filtering together, a better option is a summary report built on a shared model or composite model that combines the needed tables, published inside an app.

How do you connect Power BI Desktop to a SQL Server database?

Show answer

Use Home, Get Data, SQL Server. Enter the server name and optionally the database, choose Import or DirectQuery, and optionally paste a native SQL query under Advanced options. Choose Windows or database authentication, then pick tables in the Navigator and select Transform Data to clean them in Power Query. For scheduled refresh of an on-premises server, an on-premises data gateway must be configured in the Service.

How do you publish a report and share it with business users?

Show answer

In Desktop, choose Publish and select a workspace. In the Service, set up the semantic model's credentials and scheduled refresh. Then create or update an app from the workspace, choose the reports and audiences, and grant access to user groups. Apps give consumers a clean, stable view while authors keep working in the workspace. Avoid sharing from My workspace for team content.

Why do you want to work as a Power BI developer?

Show answer

I like turning raw data into clear answers that people can act on. In my projects I enjoyed the full flow: cleaning messy Excel and SQL data, designing a proper model, writing DAX and making reports that users actually open every day. Power BI is widely used across Indian companies and global clients, so the role offers strong growth into data analytics, data engineering with Fabric, or BI architecture.

What are Import, DirectQuery and Live Connection modes?

Show answer

Import loads a compressed copy of data into the in-memory VertiPaq engine, giving the fastest queries but requiring refresh. DirectQuery sends queries to the source at view time, so data is current but performance depends on the source and some DAX is limited. Live Connection connects a report to an existing semantic model or Analysis Services model without its own model. Composite models mix Import and DirectQuery tables.

What licence types exist for Power BI?

Show answer

Power BI Free lets a user build in Desktop and use My workspace but not share content. Power BI Pro is needed to publish to shared workspaces and share with other Pro users. Premium Per User adds larger models, more refreshes and premium features per user. Fabric capacity (F SKUs) provides dedicated capacity; at F64 and above, viewers with a free licence can consume shared content.

What is the relationship between Power BI and Microsoft Fabric?

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.

What are paginated reports and when would you use them?

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.

Users say the report shows yesterday's data even after a refresh. How do you troubleshoot?

Show answer

I would check the refresh history in the Service for errors or whether the refresh actually ran, confirm the gateway is online and has valid credentials, and verify the source itself has the latest data. Then check for cached dashboard tiles, query caching, or an incremental refresh policy that excludes recent days. Also confirm users are viewing the right workspace or app version, which may need republishing.

Your organisation has 500 report viewers. How would you plan licensing?

Show answer

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.

Tell me about a report you built that changed a business decision.

Show answer

Situation: regional managers tracked sales in separate Excel files, and stock planning was late. Task: give one daily view. Action: I combined ERP and target data in Power Query, built a star schema with DAX measures for target achievement and stock cover, and published an app with row-level security per region. Result: weekly planning moved from Friday to Monday morning, and two slow-moving products were discontinued earlier.

What is Copilot in Power BI and what should you watch out for?

Show answer

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.

Tell me about a time you had to deliver a report with unclear requirements.

Show answer

Situation: an operations head asked for a 'delivery performance dashboard' with no detail. Task: deliver something useful in a week. Action: I held a 30-minute session to list five questions they wanted answered, sketched a page layout and built a quick prototype in two days with sample data. After feedback I finalised it. Result: the dashboard went live on time and became part of the weekly operations review.

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

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.

What is Direct Lake storage mode?

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.

Your source data is 2 billion rows in a cloud warehouse and users need near real-time numbers. Which mode do you choose?

Show answer

Importing 2 billion rows is heavy and refresh-bound, so I would use DirectQuery or, on Fabric with data in OneLake, Direct Lake. To keep reports fast I would add aggregation tables in Import mode for common summary queries, keep detail in DirectQuery, and use a composite model. I would also make sure the warehouse has good indexing or clustering for the filter columns.

What is the PBIP project format and why use it?

Show answer

Power BI Project (PBIP) saves a report and model as folders of text files instead of one binary PBIX file. The semantic model is stored in TMDL or TMSL and the report in JSON definitions. This makes Git version control, code reviews, diffs and merge of changes practical, and works with Fabric Git integration and deployment pipelines. It is preferred for team development and CI/CD.

You inherit 40 reports, each with its own copy of the same sales data. How would you consolidate?

Show answer

I would identify the common tables and measures, then build one certified shared semantic model with a proper star schema and agreed measure definitions. Reports would be rebuilt or rebound as thin reports using live connections to it, starting with the most used ones from activity data. Old models are retired gradually. This cuts refresh load, removes conflicting numbers and makes changes in one place.

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

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 Power Query and where is it used?

Show answer

Power Query is the data connection and transformation engine in Power BI, Excel, dataflows and Fabric Data Factory. It lets you connect to hundreds of sources and shape data with a recorded series of steps, such as removing columns, changing types, merging and unpivoting. Behind the interface each step is written in the M language. Steps re-run automatically on every refresh, making cleaning repeatable.

What is the difference between Merge Queries and Append Queries?

Show answer

Merge is like a SQL join: it combines columns from two tables based on matching key columns, with join kinds like left outer, inner, full outer, left anti and right anti. Append stacks rows of tables with the same structure on top of each other, like a SQL UNION ALL, for example combining monthly sales files. Merge widens a table, append lengthens it.

What does Unpivot do and when would you use it?

Show answer

Unpivot turns columns into rows, creating attribute and value columns. It is used when data comes in a wide, report-style layout, such as months as separate columns Jan, Feb, Mar. Unpivoting them into a Month column and an Amount column gives a tall, tidy table that works well with relationships, a date table and DAX. Unpivot Other Columns is safer because new months are handled automatically.

Why should you set data types correctly in Power Query?

Show answer

Correct data types make sorting, filtering, aggregation and relationships work properly; text-typed numbers cannot be summed and mismatched key types break relationships. Types also affect compression and model size, for example whole numbers compress better than decimals or text. Set types once near the end of a query and avoid repeated automatic Changed Type steps, which can fail when column names change.

Every month a new Excel file arrives in a folder. How do you load all of them automatically?

Show answer

Use the Folder connector, or SharePoint Folder for files on SharePoint, pointing to the folder, filter to the right file extension and naming pattern, then use Combine Files. Power Query creates a sample-file transform function applied to every file and appends the results. New files dropped into the folder are included on the next refresh, as long as their structure matches the sample.

How do you add a conditional column in Power Query?

Show answer

Use Add Column, Conditional Column and define rules, or write M directly: = Table.AddColumn(Source, "Band", each if [Amount] >= 100000 then "High" else if [Amount] >= 25000 then "Medium" else "Low", type text) Adding the type at the end avoids an extra Changed Type step. Conditional columns built here are computed at refresh, not at query time.

How do you split a full name column and clean text in Power Query?

Show answer

Select the column, use Transform, Split Column by Delimiter (space, leftmost) to get first and last names. Then use Format, Trim and Clean to remove extra spaces and non-printable characters, and Capitalize Each Word for consistent case. In M: = Table.TransformColumns(Source, {{"Name", each Text.Proper(Text.Trim(_)), type text}})

How do you handle a business user who keeps changing report requirements?

Show answer

I first understand the goal behind the changes, because frequent changes often mean the core question was not clear. I write down agreed requirements and a mock-up, then deliver in small iterations so feedback comes early. In one project weekly 20-minute reviews cut rework a lot, and changes that were large were logged for the next version rather than added mid-sprint.

What is the difference between Power Query M and DAX?

Show answer

M is the language of Power Query, used at refresh time to extract, clean and shape data before it enters the model, such as merging tables or splitting columns. DAX is used after data is loaded, to define measures, calculated columns and security that evaluate in filter context at query time. As a rule, do row-level shaping in M or the source and dynamic calculations in DAX.

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 are query parameters in Power Query?

Show answer

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.

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 the difference between Reference and Duplicate in Power Query?

Show answer

Duplicate copies all the steps of a query into a new independent query, so later changes to the original do not affect it. Reference creates a new query whose source is the output of the original, so it inherits any changes. Reference is useful for building several tables from one staging query, such as dimension tables from a cleaned sales table, while Duplicate is for experimenting separately.

Refresh takes 90 minutes because of heavy Power Query steps. How do you speed it up?

Show answer

I would check query folding with View Native Query or the folding indicators and move non-folding steps after folding ones. Filter rows and remove unused columns as early as possible, push joins and aggregations into SQL views, avoid row-by-row custom functions over large tables, and disable load for staging queries. For large fact tables, set up incremental refresh so only recent partitions reload.

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.

A source table has duplicate customer rows with different spellings of the same name. What do you do?

Show answer

For exact duplicates, Remove Duplicates on the key column. For spelling variations, use fuzzy matching in Merge Queries with a similarity threshold and transformation table, review results, and maintain a mapping table of approved matches. The best long-term fix is to use a stable customer ID from the source system and report data quality issues to the owner rather than cleaning endlessly in Power BI.

Write an M query that filters rows, removes columns and changes types.

Show answer

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.

Create a date table in Power Query using M.

Show answer

let StartDate = #date(2022, 1, 1), EndDate = #date(2026, 12, 31), Days = Duration.Days(EndDate - StartDate) + 1, Dates = List.Dates(StartDate, Days, #duration(1, 0, 0, 0)), T = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}), Typed = Table.TransformColumnTypes(T, {{"Date", type date}}), Y = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type), M = Table.AddColumn(Y, "Month", each Date.MonthName([Date]), type text) in M

Describe a time you cleaned very messy data under a deadline.

Show answer

Situation: a client sent 36 monthly Excel files with merged headers and changing columns two days before a review. Task: build a consolidated sales report. Action: I used the Folder connector with a custom function that promoted the right header row, unpivoted month columns and mapped renamed fields through a lookup table. Result: the report was ready a day early, and future files loaded automatically with no manual work.

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 is the difference between Group By in Power Query and summarising in DAX?

Show answer

Group By in Power Query aggregates rows at refresh time and stores only the summarised table, reducing model size, which is useful when detail is never needed. Summarising in DAX through measures keeps detail in the model and aggregates dynamically for any filter at query time. Use Power Query grouping for fixed summaries such as monthly snapshots, and DAX measures when users need flexible slicing.

What are the privacy levels in Power Query and why do they cause errors?

Show answer

Privacy levels, Private, Organizational and Public, control whether data from one source can be sent to another source during combining, to avoid leaking sensitive data. When a query mixes sources with incompatible levels, Power Query may block it with a Formula.Firewall error or stop folding. The fix is to set correct privacy levels for each source or restructure queries so sources are combined in a staged way.

A refresh fails with 'column not found' after the source team renamed a column. How do you make queries more robust?

Show answer

I would fix the immediate step, then reduce dependence on exact column names: avoid hard-coded Changed Type steps on all columns, select only needed columns with MissingField.Ignore where suitable, and standardise names in one place. I would agree a data contract or SQL view with the source team so renames go through a stable interface, and set up refresh failure notifications.

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

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.

Two queries return the same data from different systems, and totals do not match. How do you investigate?

Show answer

I would compare row counts and totals at a high level first, then drill down by date and key dimensions to find where the difference starts. Merge both tables with a full outer join on the business key and use anti joins to list missing rows, then compare values on matched rows. Common causes are different cut-off times, filters, duplicates, time zones or currency conversions.

What is a star schema and why is it recommended for Power BI?

Show answer

A star schema has central fact tables holding numeric events, such as sales lines, linked to dimension tables describing them, such as Date, Product, Customer and Store. Power BI's engine and DAX are optimised for this shape: filters flow cleanly from dimensions to facts, measures are simpler, models compress better and reports run faster. Flat single tables or snowflakes usually need more complex DAX.

What is the difference between fact and dimension tables?

Show answer

Fact tables store measurable events at a defined grain, such as one row per order line, with numeric columns like quantity and amount and foreign keys to dimensions. Dimension tables store descriptive attributes used for filtering and grouping, such as product name, category, region or calendar month, with one row per key. Facts are usually long and narrow; dimensions are shorter and wider.

Explain relationship cardinality in Power BI.

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.

Why do you need a dedicated date table?

Show answer

A date table gives one row per day with columns like year, quarter, month, week and fiscal periods, so all facts can be analysed on a common calendar. Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a contiguous date column, and the table should be marked as a date table. It also avoids the many hidden auto date/time tables that bloat the model.

A business user asks to merge customer and sales into one table to make it easier. How do you respond?

Show answer

I would explain that a star schema with separate Customer and Sales tables is easier for them in practice: slicers are cleaner, totals are correct and reports run faster. To keep it user-friendly I would hide key columns, organise measures in display folders and name fields in plain language. I would show them the field list so they see simplicity on the surface.

Create a date table with DAX.

Show answer

Date = ADDCOLUMNS( CALENDAR(DATE(2022, 1, 1), DATE(2026, 12, 31)), "Year", YEAR([Date]), "Month No", MONTH([Date]), "Month", FORMAT([Date], "MMM"), "Quarter", "Q" & QUARTER([Date]), "Weekday", FORMAT([Date], "ddd") ) Then mark it as a date table and sort Month by Month No.

How do you mark a table as a date table and why?

Show answer

In Table view or Model view, select the date table, choose Table tools, Mark as date table, and select the date column, which must be unique, contiguous and without blanks. This tells Power BI to use it for time intelligence and disables the auto date hierarchy for that table. Also turn off Auto date/time under Options, Data Load to avoid hidden date tables for every date column.

How do you sort a month name column correctly?

Show answer

By default month names sort alphabetically, April first. In the Date table add a Month Number column, select the Month name column, then choose Column tools, Sort by column, Month Number. The same applies to weekday names and fiscal months. The sort column must have one value per name value, otherwise Power BI shows an error.

How do you explain a data model to a non-technical stakeholder?

Show answer

I use a simple analogy: the fact table is the list of transactions, and dimensions are the labels we use to slice them, like date, product and region. I show the model diagram with only main tables and explain that slicers work through these links. Then I demonstrate one visual changing when a slicer changes, which makes the idea concrete quickly.

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.

What are active and inactive relationships?

Show answer

Only one active relationship can exist between two tables along a filter path; additional relationships become inactive and appear as dashed lines. For example, Sales may have OrderDate and ShipDate both linked to Date, with OrderDate active. An inactive relationship is used only inside a measure with USERELATIONSHIP, such as CALCULATE([Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])).

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.

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.

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.

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.

A slicer on Product Category does not filter the Targets visual. What do you check?

Show answer

I would check the relationship path in model view: whether Targets is related to the Product or Category dimension at all, whether the relationship is active, and its cross-filter direction. If Targets are at category level, it needs a relationship to a Category table that also filters Product. Also check whether visual interactions were turned off for that slicer and whether the measure uses ALL or REMOVEFILTERS.

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.

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.

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 the grain of a fact table and why does it matter?

Show answer

Grain is the level of detail of one fact row, such as one row per order line per day. It must be defined clearly before building measures, because mixing grains in one table, for example daily sales and monthly targets, causes double counting and wrong totals. Facts at different grains should be separate tables related to dimensions at the matching level.

How do you handle a many-to-many relationship between sales and budget at different grains?

Show answer

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.

What are slowly changing dimensions and how do they affect Power BI models?

Show answer

Slowly changing dimensions track attribute changes over time. Type 1 overwrites the old value, so history shows the current attribute. Type 2 adds a new row with a surrogate key and valid-from and valid-to dates, preserving history, such as a customer moving region. In Power BI, Type 2 needs facts to carry the surrogate key, and the warehouse should handle it rather than Power Query.

Totals look inflated after adding a second fact table. What could be wrong?

Show answer

Likely a relationship is joining at the wrong grain or creating a many-to-many path, so rows get duplicated in filtering, or a bi-directional relationship is letting one fact filter another unexpectedly. I would check the relationships, keys and cardinality, make filters flow from shared dimensions only, and verify a measure against a known total in the source system using a simple table visual.

How do you use a bridge table for a many-to-many scenario?

Show answer

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.

What are surrogate keys and why are they used?

Show answer

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.

What is DAX?

Show answer

DAX, Data Analysis Expressions, is the formula language for Power BI, Analysis Services and Power Pivot. It is used to create measures, calculated columns, calculated tables and security rules. DAX looks like Excel formulas but works on tables and columns with filter context. Key functions include SUM, CALCULATE, FILTER, ALL, RELATED, time intelligence functions and iterators like SUMX.

What is the difference between a calculated column and a measure?

Show answer

A calculated column is computed row by row at refresh and stored in the model, so it uses memory and can be used in slicers, filters and relationships, for example Profit = Sales[Amount] - Sales[Cost]. A measure is computed at query time in the current filter context and is not stored, for example Total Profit = SUM(Sales[Profit]). Prefer measures for aggregations and use columns only when you need to slice by the value.

What is the difference between SUM and SUMX?

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.

What do RELATED and RELATEDTABLE do?

Show answer

RELATED fetches a value from the one side of a relationship into a row context on the many side, such as RELATED('Product'[Category]) in a Sales calculated column. RELATEDTABLE goes the other way, returning the related rows from the many side for the current row, often wrapped in COUNTROWS to count a customer's orders. Both need an existing relationship and a row context.

Why should you use DIVIDE instead of the division operator?

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 is the difference between COUNT, COUNTA, COUNTROWS and DISTINCTCOUNT?

Show answer

COUNT counts non-blank values in a column, COUNTA counts non-blank values of any type, COUNTROWS counts rows in a table, and DISTINCTCOUNT counts unique values in a column. For example, COUNTROWS(Sales) gives order lines while DISTINCTCOUNT(Sales[CustomerID]) gives active customers. COUNTROWS is generally preferred for counting records because its intent is clearer.

Write basic measures for total sales, total cost and profit margin percentage.

Show answer

Total Sales = SUM(Sales[Amount]) Total Cost = SUMX(Sales, Sales[Qty] * Sales[UnitCost]) Profit = [Total Sales] - [Total Cost] Margin % = DIVIDE([Profit], [Total Sales]) Format Margin % as a percentage. Building measures on other measures keeps logic in one place and easy to change.

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

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.

How do you learn a difficult concept like filter context?

Show answer

I learn by building tiny examples. For filter context I made a small model with five products and wrote measures with and without ALL, KEEPFILTERS and ALLSELECTED, checking each result in a table visual. I also read documentation and use DAX Studio to see queries. Explaining it to a teammate afterwards showed me which parts I had not fully understood.

Explain row context and filter context.

Show answer

Row context means DAX is evaluating one row at a time, which happens in calculated columns and iterators like SUMX, so you can refer to column values of the current row. Filter context is the set of filters applied to the model from slicers, visual rows, columns and CALCULATE. Measures are evaluated in filter context. Row context does not filter the model unless converted by context transition.

What does CALCULATE do?

Show answer

CALCULATE evaluates an expression in a modified filter context. Its filter arguments can add, replace or remove filters, for example CALCULATE([Total Sales], 'Product'[Category] = "Electronics") replaces any category filter with Electronics. It also performs context transition when used inside a row context, turning the current row into an equivalent filter. It is the most important function in DAX.

What is the difference between ALL, ALLEXCEPT and REMOVEFILTERS?

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.

What are variables in DAX and why use them?

Show answer

VAR defines a named value inside an expression and RETURN gives the final result. Variables make measures easier to read, avoid repeating the same calculation, which improves performance, and help debugging because you can return an intermediate variable. A variable is evaluated once in the context where it is defined, so its value does not change if later CALCULATE filters are applied.

A measure shows the same value on every row of a table visual. Why?

Show answer

The measure is probably ignoring the filter context of the rows, for example using ALL on that column or table, or the row field comes from a table that is not related to the fact table, so its filter cannot reach the data. I would check relationships in model view, the direction of filtering, and any ALL or REMOVEFILTERS in the measure.

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

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.

Write a measure for percentage of grand total sales.

Show answer

Sales % of Total = DIVIDE( [Total Sales], CALCULATE([Total Sales], REMOVEFILTERS('Product')) ) This gives each product's share of total across all products. Use ALLSELECTED('Product') instead if the denominator should respect slicer selections on products.

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.

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

Your measure returns an error that a table of multiple values was supplied where a single value was expected. How do you fix it?

Show answer

This happens when an expression like VALUES or a column reference returns several rows where a scalar is needed, for example in IF or comparison. I would use SELECTEDVALUE with a fallback, wrap the column in an aggregation like MAX or SUM, or use HASONEVALUE to check first. If iteration is needed, use SUMX or another iterator over VALUES instead.

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.

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.

100

A colleague's measure uses FILTER(Sales, ...) on a 50-million-row table and is slow. How would you rewrite it?

DAX FundamentalsHard

Show answer

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.

101

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.

102

Write YTD, previous year and YoY growth measures.

Advanced DAX and Time IntelligenceEasy

Show answer

Sales YTD = TOTALYTD([Total Sales], 'Date'[Date], "31/3") Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) YoY % = VAR cur = [Total Sales] VAR py = [Sales PY] RETURN DIVIDE(cur - py, py) The "31/3" argument makes YTD follow an April to March fiscal year.

103

Write a measure that shows the selected slicer value in a title.

Advanced DAX and Time IntelligenceEasy

Show answer

Report Title = VAR r = SELECTEDVALUE('Store'[Region], "All Regions") VAR y = SELECTEDVALUE('Date'[Fiscal Year], "All Years") RETURN "Sales for " & r & " - " & y Use it in a visual title through conditional formatting, Title text, Field value. SELECTEDVALUE returns the fallback text when zero or multiple values are selected.

104

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.

105

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.

106

What are field parameters in Power BI?

Show answer

Field parameters let report users switch which measure or dimension a visual shows using a slicer, for example choosing between Sales, Profit and Units, or switching the axis between Region and Category. They are created through Modeling, New parameter, Fields, which generates a calculated table. They reduce the number of duplicate visuals and bookmarks needed for dynamic reports.

107

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.

108

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.

109

What are visual calculations?

Show answer

Visual calculations are DAX calculations defined directly on a visual and computed on the visual's own aggregated data, rather than on the model. They make running totals, moving averages, percent of parent and comparison with previous rows simpler using functions like RUNNINGSUM, MOVINGAVERAGE and PREVIOUS. They do not add measures to the model, so they suit visual-specific logic but cannot be reused across visuals.

110

What is a disconnected table and how is it used for what-if analysis?

Show answer

A disconnected table has no relationship to the model and is used to capture a user's choice through a slicer. For example, a Discount table from 0 to 30 percent created with GENERATESERIES, or the What-if parameter feature, lets a measure read SELECTEDVALUE(Discount[Value]) and calculate projected revenue. Since it does not filter facts, the measure decides how the selection is applied.

111

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.

112

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.

113

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.

114

Write a dynamic rank measure for products.

Show answer

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.

115

What are calculation groups?

Show answer

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.

116

What is TREATAS and when do you use it?

Show answer

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.

117

What is the difference between ISFILTERED, ISINSCOPE and HASONEVALUE?

Show answer

ISFILTERED returns true if a column has a direct filter. HASONEVALUE returns true when exactly one value is visible, useful before SELECTEDVALUE. ISINSCOPE returns true when a column is the current grouping level in a visual hierarchy, which is the most reliable way to detect subtotal versus detail rows in matrices. For example, IF(ISINSCOPE('Product'[Product]), [Sales], BLANK()) hides subtotals.

118

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.

119

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.

120

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.

121

A measure works in a card but gives wrong results in a matrix with subtotals. How do you debug it?

Show answer

I would test the measure at each level of the matrix using a table visual and check how filter context differs at the subtotal. Often logic uses SELECTEDVALUE or MAX, which return blank or misleading results when several values are visible. I would use ISINSCOPE to branch logic or SUMX over the grouping column for additive results, and validate with DAX Studio queries.

122

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.

123

Write a measure that counts new customers in a period.

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.

124

Tell me about the most complex DAX problem you solved.

Show answer

Situation: finance needed month-end inventory balances, which are semi-additive and must not be summed across months. Task: build balances that work at any date level. Action: I wrote a measure using LASTNONBLANK over dates to take the closing balance per period and SUMX across warehouses, then validated against ERP reports for six months. Result: the report replaced a manual Excel process and matched ERP within rounding.

125

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.

126

What makes a good dashboard design?

Show answer

A good dashboard answers specific questions for a specific audience. Put the most important KPIs at the top left, follow a logical flow from summary to detail, use a few consistent colours with one highlight colour, and choose simple visuals like bar and line charts. Remove clutter such as 3D effects and heavy borders, label clearly, show context like targets or last year, and keep each page to a manageable number of visuals.

127

How do you choose the right chart type in Power BI?

Show answer

Match the chart to the question. Use bar or column charts to compare categories, line charts for trends over time, stacked bars for part-to-whole across few categories, scatter charts for relationships, maps only for true geographic patterns, cards and KPI visuals for single numbers, and tables or matrices when users need exact values. Avoid pie charts with more than four or five slices.

128

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

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.

129

What is conditional formatting and where can you apply it?

Show answer

Conditional formatting changes colours, icons, data bars or font based on values or rules, helping users spot good and bad performance quickly. You can apply it to table and matrix cells, backgrounds, font colour, chart data colours and titles using field values, rules or a measure that returns a colour code. A measure returning hex colours gives full control, such as red when below target.

130

What are report themes and why use them?

Show answer

A report theme is a JSON file that sets default colours, fonts, visual styles and backgrounds for all visuals. Using a company theme keeps reports consistent with brand guidelines and saves formatting time, since new visuals adopt it automatically. Themes can be built in Desktop's theme customiser or edited as JSON and shared across the team as a standard.

131

Leadership wants to see a red, amber, green status for each region against target. How do you build it?

Show answer

I would create a measure for achievement percentage, DIVIDE(Sales, Target), and a status measure returning a colour or icon based on thresholds agreed with leadership, such as below 90 percent red, 90 to 100 amber, above 100 green. Apply it through conditional formatting in a matrix or as icon rules. I would also add text or icons so status is clear without colour.

132

Users on mobile find the report hard to read. What do you do?

Show answer

I would create a mobile layout in Desktop through View, Mobile layout, placing the most important cards and charts in a single column with larger fonts. Remove or simplify dense tables, check slicer usability on touch screens and test in the Power BI mobile app. If mobile use is common, I might design a separate concise summary page for mobile users.

133

How do you set up drill-through to a customer details page?

Show answer

Create a new page named Customer Details, add visuals showing customer information, orders and trends, and drag Customer Name or Customer ID into the Drill through well in the Visualizations pane. Power BI adds a back button automatically. Users can now right-click a customer in any visual and choose Drill through, Customer Details, and the page opens filtered to that customer.

134

How do you design a KPI card that shows value, target and variance?

Show answer

Use the new Card visual or a KPI visual with a main measure such as Total Sales, a target measure and a variance percent measure. Add conditional formatting so the variance turns red below target and green above it, and include a small trend if space allows. Keep labels short, such as Sales vs Target, and show the comparison period in the subtitle.

135

What are bookmarks and how are they used?

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.

136

What are tooltips and report page tooltips?

Show answer

Default tooltips show field values when hovering over a data point. Report page tooltips let you design a small custom page, set as a tooltip page type, showing extra visuals like a mini trend chart for the hovered category. They add detail without cluttering the main page. Keep tooltip pages light because they run queries on every hover.

137

How do you control how visuals interact with each other?

Show answer

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.

138

How do you design reports for accessibility?

Show answer

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.

139

When would you use a custom visual and what are the risks?

Show answer

Custom visuals from AppSource add chart types not built in, such as Gantt charts, sankey diagrams or advanced KPI cards. Risks include performance issues, limited support, export and accessibility gaps, and data security, since some visuals may call external services. Prefer certified visuals, check organisational policy, since admins can restrict them, and use built-in visuals where they meet the need.

140

Users complain the report page is cluttered with 22 visuals. How do you redesign it?

Show answer

I would ask which decisions the page supports and keep only visuals that answer those questions. Then group related KPIs into a top row of cards, keep two or three main charts, and move details to drill-through or tooltip pages. Slicers can move into a collapsible panel using bookmarks. Fewer visuals also make the page load faster.

141

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

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.

142

Executives want a one-page story of quarterly performance. How do you structure it?

Show answer

I would start with three to five headline KPIs versus target and last year at the top, then a trend chart showing the quarter's movement, then a breakdown by region or product showing what drove the change, and finally a short text box or smart narrative stating key takeaways and actions. Each element answers the next natural question, and details live on drill-through pages.

143

How do you create a measure-driven conditional colour?

Show answer

Sales Colour = VAR ach = DIVIDE([Total Sales], [Target]) RETURN SWITCH(TRUE(), ach < 0.9, "#D64550", ach < 1, "#F2A93B", "#2E9E5B") In the visual, open conditional formatting for background or font colour, choose Format style Field value and select this measure.

144

How do you build a toggle between a chart and a table using bookmarks?

Show answer

Place a chart and a table in the same position. In the Selection pane hide the table and create a bookmark named Chart View with Data unchecked so filters are not saved; then show the table, hide the chart and create Table View. Add two buttons and set each button's action to the matching bookmark. Group the visuals to keep the layout tidy.

145

How do you create a dynamic axis using field parameters?

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.

146

How do you build a custom tooltip page?

Show answer

Create a new page, set Page information, Allow use as tooltip on, and set Canvas settings to the Tooltip size. Add small visuals like a monthly trend line for the hovered item. On the main visual, go to Format, Tooltips, set Type to Report page and select the tooltip page. Fields in the main visual automatically filter the tooltip page on hover.

147

Tell me about a time a user rejected your report design. What did you do?

Show answer

Situation: sales managers stopped using a dashboard I built. Task: find out why. Action: I watched two managers use it and found they needed weekly target gaps first, which were hidden on page three. I moved those to the top, removed four rarely used charts and added drill-through for details. Result: weekly usage in the activity log tripled within a month.

148

How do you ensure your report numbers are trusted by finance users?

Show answer

I reconcile key measures with the finance system for several periods before launch and document definitions, such as whether revenue is net of returns and taxes, in measure descriptions and a glossary page. I add a refresh timestamp to each page and set up data quality checks. When differences appear, I explain them quickly. Certified semantic models signal that numbers went through review.

149

What is a decomposition tree and when is it useful?

Show answer

The decomposition tree is an AI visual that lets users break down a measure across several dimensions in any order, for example splitting a sales drop by region, then category, then channel. It can also suggest the next split automatically using high value or low value analysis. It is useful for root-cause exploration by business users, though it is less suited to fixed reporting.

150

A matrix with 200,000 rows makes the report slow and unusable. What would you suggest?

Visualisation and Report DesignHard

Show answer

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.

151

What is row-level security in Power BI?

Show answer

Row-level security restricts which rows of data a user can see in a semantic model. You define roles in Desktop with DAX filter expressions on tables, such as Store[Region] = "North", then assign users or security groups to roles in the Service. Filters propagate through relationships to fact tables. RLS applies to viewers; workspace Admins, Members and Contributors can see all data.

152

What is an on-premises data gateway and when is it needed?

Show answer

The on-premises data gateway is software installed on a machine inside the company network that securely connects the Power BI Service to on-premises sources such as SQL Server, file shares or Oracle. It is needed for scheduled refresh and DirectQuery to those sources. Standard mode supports many users and is centrally managed; personal mode is for a single user. Cloud sources usually do not need a gateway.

153

What are subscriptions and data alerts in the Power BI Service?

Show answer

Subscriptions email a snapshot of a report page or dashboard to users on a schedule, or after data refresh, with a link to the live content. Data alerts notify users when a value on a dashboard card, KPI or gauge tile crosses a threshold, such as daily sales below a target. Paginated reports support subscriptions with attachments like PDF or Excel. Power Automate can extend alerts into workflows.

154

How do you test row-level security?

Show answer

In Desktop use Modeling, View as, select a role and optionally Other user with an email to simulate dynamic RLS, and check visuals show only expected rows. In the Service open the semantic model's Security settings, select a role and use Test as role. Test with real sample users from different regions and a user with no mapping, who should see no data, and confirm totals match expectations.

155

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.

156

How do you manage multiple report requests from different departments with tight deadlines?

Show answer

I log every request with its business goal, deadline and effort, and agree priorities with my manager instead of choosing alone. I reuse shared semantic models so new reports are faster, and I communicate realistic timelines early. In one quarter this helped me deliver eight reports, with two lower-priority ones moved by a week after discussion with requesters, without escalations.

157

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.

158

What is the difference between sharing a report, publishing an app and using a workspace?

Show answer

A workspace is where creators collaborate. Direct sharing gives one person or group access to a single report, which is quick but hard to govern at scale. An app packages reports and dashboards from a workspace for consumers, with audiences controlling who sees which content, and updates only when republished. Apps are the recommended way to distribute content to large business audiences.

159

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.

160

What is incremental refresh?

Show answer

Incremental refresh partitions a large table by date so each refresh reloads only recent data, such as the last 10 days, while keeping older partitions, such as five years of history, unchanged. It uses the RangeStart and RangeEnd datetime parameters to filter the query, which must fold to the source. It cuts refresh time and load on sources, and can add a DirectQuery partition for real-time data.

161

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.

162

Each regional manager must see only their region, and the CEO must see everything. How do you implement this?

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.

163

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.

164

Business users keep asking for edits directly in production reports. How do you set up a better process?

Show answer

I would set up separate Development, Test and Production workspaces with a deployment pipeline, store PBIP files in Git, and route requests through a simple backlog. Changes are built in Dev, reviewed by a key user in Test and deployed to Production on a release schedule. Users consume through an app, so production stays stable and changes are traceable.

165

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

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.

166

How do you mentor junior Power BI developers in your team?

Show answer

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.

167

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.

168

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.

169

What is object-level security?

Show answer

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.

170

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.

171

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

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.

172

Users export large amounts of sensitive data from reports to Excel. How do you control it?

Show answer

I would review whether export is really needed and adjust export settings per report or at tenant level, for example allowing only summarised data. I would apply Purview sensitivity labels so exported files keep protection, use RLS and OLS to limit what can be exported, and monitor exports through the activity log. For legitimate detailed needs, a governed paginated report is better.

173

How do you configure incremental refresh on a sales table?

Show answer

In Power Query create RangeStart and RangeEnd parameters of type Date/Time, and filter OrderDate so it is greater than or equal to RangeStart and less than RangeEnd. Confirm the step folds to the source. In Desktop right-click the table, choose Incremental refresh, set to archive, say, 5 years and refresh the last 10 days, optionally detect data changes. Publish, and the Service creates partitions on first refresh.

174

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)

175

A report must be embedded in a customer-facing web portal for users without Power BI licences. What approach do you take?

Show answer

I would use Power BI Embedded with the 'embed for your customers' pattern, also called app owns data. The application authenticates with a service principal, generates embed tokens and passes customer identity for RLS, so each customer sees only their data. It needs Fabric or embedded capacity. I would design RLS carefully, test token expiry and monitor capacity load as the customer base grows.

176

What modelling practices reduce Power BI model size?

Show answer

Remove unused columns and rows, avoid high-cardinality columns like GUIDs and datetime with seconds by splitting date and time or rounding, use integer surrogate keys, prefer measures over calculated columns, turn off Auto date/time, and choose the right data types, such as fixed decimal. Load only the history users need, and summarise data where detail is not required.

177

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.

178

How do you use Performance Analyzer in Power BI Desktop?

Show answer

Open View, Performance Analyzer, click Start recording and then Refresh visuals or interact with the page. Each visual shows total duration split into DAX query, visual display and other. Sort by duration to find the slowest visuals, expand them and choose Copy query to paste into DAX Studio for deeper analysis. Clear the cache between tests so timings are realistic.

179

How do you stay current with frequent Power BI updates?

Show answer

Power BI releases features monthly, so I read the monthly update blog and Fabric release notes, try new features on a test report, and follow community experts and forums. I keep a short list of features relevant to our team, such as visual calculations or TMDL view, and share a five-minute demo in team meetings when something will save us effort.

180

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.

181

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.

182

What does the Best Practice Analyzer do?

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.

183

A report page takes 25 seconds to load. Walk me through how you would fix it.

Show answer

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.

184

How do you analyse model size with DAX Studio?

Show answer

Connect DAX Studio to the open Power BI Desktop file, go to Advanced, View Metrics to run VertiPaq Analyzer. It shows size per table and column, cardinality, encoding and relationship sizes. Sort columns by total size to find the biggest consumers, often IDs, timestamps or long text. You can export a vpax file to compare before and after optimisation or share with colleagues.

185

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.

186

How do you run a DAX query in DAX Studio to test a measure?

Show answer

EVALUATE SUMMARIZECOLUMNS( 'Date'[Fiscal Year], 'Product'[Category], "Sales", [Total Sales], "Margin", [Margin %] ) ORDER BY 'Date'[Fiscal Year] Turn on Server Timings and Clear cache before running to see storage engine and formula engine time. The same EVALUATE syntax works in the DAX query view in Desktop.

187

Split a datetime column to reduce cardinality in Power Query.

Show answer

= 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.

188

How do you explain a technical limitation to a business user who wants something impossible?

Show answer

I acknowledge the goal, explain the limitation in simple terms and offer an alternative that meets most of the need. For example, when a user wanted live updates every second on a DirectQuery report over a slow system, I explained the load it would create and offered a 15-minute refresh plus an alert for threshold breaches. They accepted because the key decision was covered.

189

Users need to filter a report by 5,000 product names using a slicer, and it is slow. What do you do?

Show answer

A slicer with thousands of values sends heavy queries and is hard to use. I would add a hierarchy so users pick category and subcategory first, enable the slicer search box, and consider a text filter visual for direct search. Apply buttons in query reduction settings stop queries on every click. If the slicer depends on a measure-filtered list, I would simplify that logic.

190

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.

191

What are the formula engine and storage engine in Power BI?

Show answer

The storage engine, VertiPaq or DirectQuery to the source, retrieves and aggregates data quickly and in parallel. The formula engine handles complex DAX logic that the storage engine cannot, working single-threaded on intermediate results. Fast measures push most work to the storage engine. DAX Studio's server timings show time spent in each, helping identify measures that create large intermediate data caches for the formula engine.

192

What are aggregation tables and how do they improve performance?

Show answer

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.

193

What is the Dual storage mode?

Show answer

Dual mode tables act as Import or DirectQuery depending on the query. In a composite model, dimension tables set to Dual can be served from memory when joined to Import aggregation tables, and sent to the source when joined to DirectQuery fact tables, avoiding slow cross-source joins. It is commonly used for dimensions in models that mix aggregations with DirectQuery detail.

194

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.

195

The model is 9 GB and refresh times out. What would you do?

Show answer

I would analyse it with VertiPaq Analyzer to find the largest columns and tables, remove unused columns, split or round datetime columns, replace text keys with integer keys and drop history users do not need. Then set up incremental refresh on large fact tables so only recent partitions reload. If needed, push heavy transformations upstream into the warehouse and consider Direct Lake on Fabric.

196

A DirectQuery report is slow and the database team complains about load. What do you do?

Show answer

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.

197

Reports slow down every morning at 9 a.m. on shared capacity. What could be happening?

Show answer

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.

198

Your measure using IF and many nested CALCULATE calls is slow. How do you optimise it?

Show answer

I would store repeated sub-expressions in variables so they are computed once, replace nested IF chains with SWITCH(TRUE(), ...), use column filters instead of FILTER over tables, and avoid context transition inside large iterators. I would also check whether some logic can move into a calculated column or Power Query at refresh time. DAX Studio timings confirm whether formula engine time drops.

199

Tell me about a time you improved the performance of a slow report.

Show answer

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.

200

A large model's measures are fast in Desktop but slow in the Service. What could explain it?