31.3 EY

M22. Explain the CALCULATE function.

Reported for: EY [S5] · also Mu Sigma [S30], TCS [S13]

CALCULATE evaluates an expression in a modified filter context. Its filter arguments add, replace or remove filters, and when it is used inside a row context it performs context transition (the current row becomes a filter).

Pune Sales = CALCULATE([Total Sales], DarkStore[City] = "Pune")
Sales All Cities = CALCULATE([Total Sales], REMOVEFILTERS(DarkStore[City]))

With a Nashik slicer selected, Pune Sales still shows Pune, because the filter on City is replaced (Module 13.8).

M23. What is the difference between SUM and SUMX?

Reported for: EY [S5] [S6] · also Capgemini [S23], Amazon [S32], Accenture [S20], PwC [S11]

SUM(Orders[Amount]) adds one column. SUMX(table, expression) is an iterator: it evaluates an expression row by row and then adds the results.

Revenue at List Price = SUMX(Orders, Orders[Quantity] * RELATED(Product[Unit Price]))

Use SUM when the value already exists in a column, and SUMX when you must calculate per row first.

M24. Compare DATEADD with a period function such as DATESINPERIOD. How is PARALLELPERIOD different from SAMEPERIODLASTYEAR?

Reported for: EY [S5] [S6]

• DATEADD('Date'[Date], -1, MONTH) shifts the current selection of dates by an interval (March 1–15 becomes February 1–15).
• DATESINPERIOD builds a window of n intervals from a date (the last 3 months).
• SAMEPERIODLASTYEAR('Date'[Date]) is the same as DATEADD('Date'[Date], -1, YEAR): it shifts the exact dates by one year.
• PARALLELPERIOD('Date'[Date], -1, YEAR) returns the whole previous period. If March 2026 is selected, it returns all of 2025.

M25. What are Pivot and Unpivot? Give examples.

Reported for: EY [S5] · also Genpact [S29] (columns-to-rows in SQL)

Unpivot turns columns into rows: a target sheet with one column per month (Apr, May, Jun…) becomes two columns, Month and Target, which is what the model needs. Pivot does the opposite: turns the values of a column into new columns, for example Payment Mode values into UPI, Card and Cash columns for an export (Module 7.17).

M26. What is the difference between a table and a matrix visual?

Reported for: EY [S5]

A table is a flat list with one row per combination of fields. A matrix is a pivot table: it has row and column groups, subtotals, drill down, expand/collapse and stepped layout. Example: a matrix with City → Area on rows and Month on columns showing [Total Sales].

M27. Why is the semantic model important in enterprise BI, and which licences have you used?

Reported for: EY [S6] · also Wipro [S18], KPMG [S9], Genpact [S29]

A semantic model (formerly dataset) is the shared layer of tables, relationships, measures and security. One certified model can serve many reports, so "Total Sales" means the same everywhere. Licences at a high level: Free (personal use), Pro (publish and share with other Pro users), Premium Per User, and capacity (Premium/Fabric) where viewers may not need Pro, depending on capacity size. Licensing details change, so mention that you check the current Microsoft documentation (Module 1.4).

M28. Write DAX for a running total (cumulative sales) and YoY %.

Reported for: EY [S6] · also TCS [S14], Infosys [S15], Mu Sigma [S30]

Cumulative Sales =
CALCULATE([Total Sales],
    FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date])))

Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

Both need a marked, continuous Date table. For a running total within the year, use TOTALYTD([Total Sales], 'Date'[Date]).

M29. What is the difference between a view and a stored procedure in SQL?

Reported for: EY [S5] · also Tech Mahindra [S24] (stored procedures)

A view is a saved SELECT query that behaves like a virtual table. You can select from it, join it and (in Power BI) import it, and Power Query can fold further steps into it. A stored procedure is a saved program that can take parameters, run several statements, change data and return results. For Power BI, views are usually preferred because they are simple and foldable. Stored procedures can be called with native SQL, but that breaks query folding.

M30. SQL: return all employees, including those without a department (LEFT JOIN and NULLs).

Reported for: EY [S5]

Our version: all dark stores, including those with no orders yet.

SELECT s.StoreID, s.City, COALESCE(SUM(o.Amount), 0) AS TotalSales
FROM dbo.DarkStore AS s
LEFT JOIN dbo.Orders AS o ON o.StoreID = s.StoreID
GROUP BY s.StoreID, s.City;

LEFT JOIN keeps every row from the left table. COALESCE turns the NULL (no matching orders) into 0.

M31. SQL: top 3 highest earners per department (only for Finance), and remove duplicates without DISTINCT.

Reported for: EY [S6]

Our version: top 3 products by sales in each city, only for Pune, and duplicate order lines.

WITH ranked AS (
  SELECT s.City, o.ProductID, SUM(o.Amount) AS Sales,
         DENSE_RANK() OVER (PARTITION BY s.City ORDER BY SUM(o.Amount) DESC) AS rnk
  FROM dbo.Orders o JOIN dbo.DarkStore s ON s.StoreID = o.StoreID
  WHERE s.City = 'Pune'
  GROUP BY s.City, o.ProductID)
SELECT * FROM ranked WHERE rnk <= 3;

-- duplicates without DISTINCT: keep one row per Order ID + Product ID
WITH d AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID, ProductID ORDER BY OrderDateTime) AS rn
  FROM dbo.Orders)
SELECT * FROM d WHERE rn = 1;          -- or DELETE FROM d WHERE rn > 1

GROUP BY on all columns is another way to remove duplicates without DISTINCT.

M32. How do you keep development and testing separate when moving between environments?

Reported for: EY [S7] · also Tech Mahindra [S25], Genpact [S29]

Use separate workspaces (Dev, Test/UAT, Prod), ideally with deployment pipelines. Parameterise the server and database names (Module 10.4) and set deployment rules so the UAT stage points to the UAT database. Test the numbers against the source, test RLS with View as, and get sign-off before promoting. Keep versions of the PBIX or project files (PBIP with Git) so you can roll back.