Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Power Pivot, Data Model and DAX Interview Questions for Advanced Excel

25 Power Pivot, Data Model and DAX questions with answers for advanced excel roles — 3 easy, 6 medium, 16 hard.

About Power Pivot, Data Model and DAX

The Data Model lets Excel handle millions of rows across related tables. This topic covers star schema, relationships, calculated columns versus measures, CALCULATE, filter and row context, time intelligence for Indian financial years, percent of total, running totals and CUBE functions. These skills carry straight over to Power BI.

Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

What are Power Pivot and the Excel Data Model?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
The Data Model is an in-memory database inside the workbook, powered by the VertiPaq engine, which compresses data by column and can handle millions of rows, far beyond the sheet limit. Power Pivot is the window used to manage it: import tables, create relationships, and write DAX measures and calculated columns. Pivot tables built on the Data Model can combine several related tables without VLOOKUP.
02

Write basic DAX measures for total sales, order count and average order value.

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
In Power Pivot > Measures > New Measure, or in the pivot field list: Total Sales := SUM(Sales[Amount]) Orders := DISTINCTCOUNT(Sales[OrderID]) Avg Order Value := DIVIDE([Total Sales],[Orders]) Referencing measures inside other measures keeps logic in one place. Set the number format on the measure itself, so every pivot that uses it shows the same format.
03

Why use DIVIDE instead of the / operator in DAX?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
DIVIDE handles division by zero safely and returns blank, or a value you choose, instead of an error: Margin % := DIVIDE([Total Profit],[Total Sales]) Margin % := DIVIDE([Total Profit],[Total Sales],0) Blank results are hidden in pivots, which keeps reports clean when a product has no sales in a month. Use the / operator only when the denominator can never be zero, because it is slightly faster.
04

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

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
A calculated column is computed row by row when data is refreshed and stored in the table, like Sales[Margin] = Sales[Amount] - Sales[Cost]. It uses memory and can be used in rows, columns and slicers. A measure is computed on the fly for each cell of a pivot based on its filters, like Total Margin = SUM(Sales[Margin]). Use measures for aggregations and calculated columns only for attributes you slice by.
05

Why do you need a separate Date table in the Data Model?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a continuous date column with no gaps, covering full years. A Date table also adds columns like Month Name, Fiscal Year and Quarter for slicing. Create it with Power Query or DAX, for example: Date = CALENDAR(DATE(2024,4,1),DATE(2027,3,31)) Then mark it as the date table in Power Pivot and relate it to every fact table's date column.
06

You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Create a relationship from Sales[ProductID] to Products[ProductID], then add a calculated column in Sales: Category = RELATED(Products[Category]) RELATED follows the many-to-one relationship and works only in a row context. Often you do not need the column at all, because the pivot can use Products[Category] directly through the relationship, which saves memory.
07

What is the difference between RELATED and RELATEDTABLE?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
RELATED is used on the many side to fetch a single value from the one side, such as getting a product's category in each sales row. RELATEDTABLE is used on the one side to return all matching rows from the many side, so in Products you could write Order Count = COUNTROWS(RELATEDTABLE(Sales)). Both need an existing relationship and a row context.
08

Users of your Data Model pivot keep dragging Year, Quarter and Month separately and make mistakes. How do you make it easier?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Create a hierarchy in the Date table in Power Pivot diagram view, such as Year > Quarter > Month, so users drag one field and drill down. Set Month Name to sort by Month Number so months do not appear alphabetically. Hide key columns and raw fields that should not be used directly, and give measures clear business names. This keeps the field list small and the pivots consistent.
09

How is the Excel Data Model related to Power BI?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Both use the same VertiPaq engine and the DAX language, and both use Power Query for data preparation. A Power Pivot model can be imported into Power BI Desktop through File > Import, carrying queries, relationships and measures. This makes Advanced Excel with Power Pivot a natural stepping stone, since most of the model and DAX skills transfer directly to Power BI.
10

What is a star schema and why is it recommended for the Data Model?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
A star schema has one central fact table holding events and numbers, such as Sales with date, product ID, customer ID and amount, surrounded by dimension tables that describe them, such as Date, Product and Customer. Each dimension links to the fact table with a one-to-many relationship. It keeps the model small, makes filters flow predictably and keeps DAX measures simple.
11

Explain relationships, cardinality and filter direction in the Data Model.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
A relationship links a key column in one table to a matching column in another. Cardinality describes how many rows match: one-to-many is the standard case, where each product appears once in Products but many times in Sales. In Excel's Data Model, filters flow from the one side to the many side, so selecting a category in Products filters Sales, but not the reverse.
12

What does CALCULATE do in DAX?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
CALCULATE evaluates an expression under modified filters. It is the most important DAX function: North Sales := CALCULATE([Total Sales],Region[Region]="North") Online Sales 2026 := CALCULATE([Total Sales],Sales[Channel]="Online",'Date'[Year]=2026) Each filter argument replaces any existing filter on that column, while filters on other columns from the pivot still apply. Wrapping a filter in KEEPFILTERS adds to the existing filter instead of replacing it.
13

Explain row context, filter context and context transition.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Filter context is the set of filters applied to a calculation, coming from pivot rows, columns, slicers and CALCULATE. Row context exists when DAX iterates rows, as in a calculated column or SUMX, and lets you refer to the current row's values; it does not filter other tables by itself. Context transition happens when CALCULATE, or a measure reference, is used inside a row context and turns the current row into an equivalent filter context.
14

What is the difference between SUM and SUMX?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
SUM adds a single column. SUMX iterates a table, evaluates an expression for each row and adds the results: Revenue := SUMX(Sales,Sales[Qty]*Sales[Price]) This avoids storing a Qty times Price calculated column. Other X functions such as AVERAGEX, MAXX and COUNTX work the same way. Iterating a very large table with complex expressions can be slower, so keep the expression simple.
15

Write DAX measures for year-to-date sales and sales for the same period last year.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
With a proper Date table marked as a date table and related to Sales: Sales YTD := TOTALYTD([Total Sales],'Date'[Date],"3/31") Sales LY := CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) YoY % := DIVIDE([Total Sales]-[Sales LY],[Sales LY]) The "3/31" year-end argument gives an Indian April to March financial year; without it, YTD resets on 1 January.
16

Write a DAX measure for each product's percentage of total sales.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Remove the product filter in the denominator so it always returns the overall total: % of All Products := DIVIDE([Total Sales], CALCULATE([Total Sales],ALL(Products))) Use ALLSELECTED(Products) instead of ALL if the percentage should respect slicer selections, so the visible products add up to 100 percent. Other filters such as region or month still apply in both versions.
17

Explain ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
ALL removes all filters from a table or column and can also return all its rows. REMOVEFILTERS does the same filter removal but is clearer in intent and can only be used as a CALCULATE modifier. ALLEXCEPT removes filters from a table except the columns you name, such as keeping Region. ALLSELECTED removes filters coming from inside the pivot but keeps outer slicer and filter selections, which suits visual totals.
18

Your manager wants a fixed-format P&L with rows in a custom order, but the data is in the Data Model and the pivot keeps reshaping itself. What do you do?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Use CUBE functions instead of a pivot. Select the pivot and use PivotTable Analyze > OLAP Tools > Convert to Formulas, or write them directly, for example CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", $A5, B$3). Each cell becomes an independent formula, so you can reorder rows, insert subtotal lines and apply fixed formatting, while numbers still refresh from the model.
19

A measure shows the same value on every row of the pivot. What is wrong?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
The field in the rows is not filtering the table the measure uses. Usually there is no relationship between the two tables, the relationship is inactive, or it points the wrong way, for example the row field comes from the many side of a different fact table. Excel often shows a warning that relationships may be needed. Check Manage Relationships and use fields from dimension tables linked to the fact table.
20

Write a month-over-month growth percentage measure.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Use DATEADD on the Date table to shift the period one month back: Sales PM := CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH)) MoM % := DIVIDE([Total Sales]-[Sales PM],[Sales PM]) Place Month from the Date table in the pivot rows. For the first month in the data, Sales PM is blank, so DIVIDE returns blank rather than an error.
21

Tell me about a time you had to explain a DAX number that looked wrong to a manager.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Situation: a manager said our margin percentage in the Data Model pivot did not match his calculator. Task: show whether the measure was right. Action: I took one region, showed that he was averaging row percentages while the measure divided total margin by total sales, and walked through both in a small table. Result: he agreed the measure was correct, and I added a note to the report explaining how the KPI is calculated.
22

A workbook with the Data Model has grown to 300 MB. How do you reduce its size?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
VertiPaq size depends mostly on columns and how many unique values each has. Remove columns not used in reports, especially IDs and free text. Split date-time columns into date and time or drop the time, round decimals to required precision, and replace calculated columns with measures. Use integer keys instead of long text keys, and load only the years required. Filtering at Power Query level avoids loading unneeded rows.
23

Write a running total measure for cumulative sales across dates.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Keep all dates up to the last date in the current filter: Running Sales := CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date]<=MAX('Date'[Date]))) Place Month or Date from the Date table in the pivot rows. To restart every financial year, use TOTALYTD with the "3/31" year-end instead.
24

Tell me about a time you had to learn a new tool quickly to deliver a project.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Situation: our sales data crossed two million rows and normal pivots crashed. Task: deliver a monthly dashboard within two weeks. Action: I learned Power Pivot and DAX through documentation and practice files, built a star schema with a Date table, wrote measures for YTD and growth, and checked every number against old reports. Result: the dashboard refreshed in under a minute, and I ran a short session to train two colleagues.
25

Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Do not relate the two fact tables directly. Create shared dimension tables at a grain both can link to: a Category table and a Date table with a Month column. Relate Budget[Category] and Sales via Products to Category, and both date columns to the Date table, using the first day of the month for budgets. Then measures [Total Sales] and [Total Budget] work together when the pivot uses Category and Month.

Frequently asked questions

How important is Power Pivot, Data Model and DAX in Advanced Excel interviews?

The Data Model lets Excel handle millions of rows across related tables. This topic covers star schema, relationships, calculated columns versus measures, CALCULATE, filter and row context, time intelligence for Indian financial years, percent of total, running totals and CUBE functions. These skills carry straight over to Power BI.

How should I use the Power Pivot, Data Model and DAX sheet?

Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.

More question sheets

Excel Fundamentals and Formatting questionsLogical, Text and Date Functions questionsLookup and Dynamic Array Functions questionsPivot Tables and Data Summaries questionsCharts and Dashboards questionsPower Query and Data Cleaning questionsMacros and VBA Automation questions