31.2 Deloitte

M1. Walk me through your project, your role and the data sources you used. What was the data size?

Reported for: Deloitte [S1] [S3] · also EY [S5], Mu Sigma [S30]

Answer with the 60-second structure from 31.1. Name the sources precisely (for example "SQL Server tables for orders, an Excel file for store targets, and a SharePoint folder of monthly files"). Describe size in rows and model size if you know them (for example "about 2 million order lines; the PBIX was roughly 150 MB"). If you don't know an exact number, say so and give the order of magnitude.

M2. What is the difference between DATESBETWEEN and DATESINPERIOD?

Reported for: Deloitte [S1]

Both return a table of dates to use as a filter inside CALCULATE.

• DATESBETWEEN('Date'[Date], start, end) uses an explicit start and end date.
• DATESINPERIOD('Date'[Date], start, n, interval) starts at a date and moves n intervals forward or backward.

Sales Last 3 Months =
CALCULATE([Total Sales],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))

Sales Diwali Week 2025 =
CALCULATE([Total Sales],
    DATESBETWEEN('Date'[Date], DATE(2025,10,18), DATE(2025,10,24)))

Use DATESINPERIOD for rolling windows and DATESBETWEEN for fixed campaign periods.

M3. Explain bookmarks with a scenario.

Reported for: Deloitte [S1] [S4] · also KPMG [S8], Capgemini [S22], Wipro [S17] [S18], PwC [S11]

A bookmark saves the state of a page (filters, slicers, visibility of visuals, drill state). Scenario: on the Blinkit Delivery page, one button shows a chart of average delivery time by city and another button shows the same data as a table. Two bookmarks capture "chart visible, table hidden" and the reverse, with Data unticked so the user's city slicer is not reset. A third bookmark acts as a "Reset filters" button (Module 19).

M4. What is cardinality, and what types are there?

Reported for: Deloitte [S1] [S4] · also Accenture [S21], Capgemini [S23], Tech Mahindra [S24], Amazon [S32]

Cardinality describes how rows in two related tables match: one-to-many (1:*), many-to-one, one-to-one (1:1) and many-to-many (*:*). In our model, DarkStore → Orders is one-to-many: one store (BLK-PUN-01) has many order lines. Cardinality also means the number of distinct values in a column. High-cardinality columns (Order ID, DateTime) compress poorly and make models bigger (Module 27.3).

M5. If a PBIX file is slow, what could be the causes and how would you fix it?

Reported for: Deloitte [S1] [S2] [S4] · also Capgemini [S22], PwC [S11], Tech Mahindra [S25], Amazon [S32]

Work in layers and measure first:

1. Measure: Performance Analyzer shows the DAX query time, visual display time and "other" time per visual. Copy slow queries to DAX Studio.
2. Model: remove unused columns, split DateTime into Date and Time, reduce cardinality, use a star schema, avoid bi-directional and many-to-many relationships where possible, turn off Auto date/time.
3. DAX: use variables, avoid FILTER over whole fact tables when a simple column filter works, prefer DIVIDE, avoid heavy iterators over millions of rows.
4. Report: fewer visuals per page (especially cards and slicers), use the Apply button on slicers or filters, avoid huge tables with thousands of rows.
5. Data volume: aggregate at source, incremental refresh, aggregations or a composite model.

Close with a check: re-run Performance Analyzer and compare timings.

M6. What is a gateway, and what types exist?

Reported for: Deloitte [S1] · also Infosys [S15] [S16], Wipro [S18], Tech Mahindra [S24], Genpact [S29], Mu Sigma [S30]

The on-premises data gateway is a Windows service that securely connects the Power BI Service to data sources inside a private network (an office SQL Server, a shared drive). Types: standard (enterprise) mode, shared by many users and data sources and centrally managed; and personal mode, for one user, Import only. A virtual network (VNet) data gateway also exists for sources inside Azure virtual networks. Cloud sources such as SharePoint Online do not need a gateway (Module 4.8).

M7. What joins can you do in Power BI?

Reported for: Deloitte [S1] [S4]

In Power Query, Merge Queries supports six join kinds: Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti (Module 7.22). Fuzzy matching is an option. In the model, relationships behave like a left outer join from the fact table to the dimension (unmatched rows show under a (Blank) member). In DAX you can also use NATURALINNERJOIN and NATURALLEFTOUTERJOIN on tables, but these are rarely needed.

M8. How do you handle a many-to-many relationship?

Reported for: Deloitte [S1] [S4] · also Accenture [S20], Capgemini [S23], EY [S5], Genpact [S29], KPMG [S8]

First ask why it is many-to-many. Usually the fix is a proper dimension (bridge) table with unique keys. For example, if a Targets table (City, Month) and Orders both have many rows per city, build a unique City dimension from DarkStore and relate both tables to it (one-to-many on each side). For true many-to-many (a customer belonging to several loyalty segments), use a bridge table (Customer ID, Segment) and set the filter direction carefully. Power BI also allows a direct *:* relationship, but it can give confusing totals and ambiguity, so use it deliberately and test the totals.

M9. A client says the visuals load too slowly with large data volumes. How would you approach it on the go?

Reported for: Deloitte [S2]

Clarify the scope (which pages, which users, since when), then follow M5. Quick wins that are safe to apply fast: remove unused visuals and columns, add the Apply button to slicers, reduce Top N rows in tables, and check that no visual shows row-level detail by default. Longer-term fixes (aggregation tables, incremental refresh, SQL views) go into a plan that you agree with the client.

M10. Slicers and filters make the dashboard very slow. What could be the cause?

Reported for: Deloitte [S2]

Common causes: slicers on high-cardinality columns (Customer Name with lakhs of values), many slicers each firing queries, bi-directional relationships spreading filters widely, heavy measures on every visual being recalculated, and DirectQuery sources with slow SQL. Fixes: slicers on dimension columns with fewer values, the Apply button (Query reduction options in File › Options), single-direction relationships, and simpler measures.

M11. Which tools show exactly which visual is slow and why?

Reported for: Deloitte [S2]

Inside Power BI: Performance Analyzer (View tab). Outside: DAX Studio (query timings, server timings, VertiPaq Analyzer for model size per column) and Tabular Editor with the Best Practice Analyzer rules. For DirectQuery, the database's own tools (execution plans in SQL Server) show slow SQL.

M12. How would you change a normal month into a financial month (April–March)?

Reported for: Deloitte [S3]

In the Date table add:

Fiscal Month No = MOD(MONTH('Date'[Date]) - 4, 12) + 1      -- April = 1, March = 12
Fiscal Year =
VAR y = YEAR('Date'[Date])
RETURN IF(MONTH('Date'[Date]) >= 4,
          "FY " & y & "-" & RIGHT(y + 1, 2),
          "FY " & (y - 1) & "-" & RIGHT(y, 2))

Sort Month Name by Fiscal Month No so the axis starts in April. For year-to-date, pass the year-end date: TOTALYTD([Total Sales], 'Date'[Date], "31/3").

M13. Explain Append vs Merge, and what is a fuzzy merge?

Reported for: Deloitte [S3] · also Infosys [S15], Capgemini [S23], EY [S5], KPMG [S8], TCS [S13]

Append stacks rows of tables with the same structure (Blinkit January orders + February orders). Merge joins columns from another table using a key (Orders + Product on Product ID). Fuzzy merge matches keys that are similar rather than identical, using a similarity threshold, for example "Kolapur" vs "Kolhapur" or "Sambhajinagar" vs "Sambhaji Nagar". Use it for one-off matching of messy text, then fix the source or use a mapping table, because fuzzy results can change when the data changes.

M14. How do you combine (union) two lists or columns in Power BI?

Reported for: Deloitte [S3]

In Power Query: Append the two tables, or in M List.Union({ListA, ListB}) (removes duplicates) or List.Combine (keeps them). In DAX: Cities = DISTINCT(UNION(VALUES(DarkStore[City]), VALUES(Customer[City]))). UNION keeps duplicates, so wrap it in DISTINCT for a unique list.

M15. For a very large e-commerce dataset, would you choose DirectQuery or Import? Why?

Reported for: Deloitte [S3] · also LTIMindtree [S27] [S28], Tech Mahindra [S24], Wipro [S19]

Default to Import when the data fits (after removing unused columns and aggregating), because it is fastest and supports all DAX. Choose DirectQuery when data is too large to import, must be near real-time, or must stay in the source for governance, and the database is fast and well indexed. For very large data, a composite model often works best: Import aggregated tables (daily sales by city and category) and keep the detailed table in DirectQuery, or use incremental refresh on an Import model.

M16. How would you design a dashboard comparing Actual vs Target sales across regions?

Reported for: Deloitte [S4] · also Mu Sigma [S30]

Load a Targets table (City, Month, Target Sales) and relate it to shared City and Date dimensions. It is at a coarser grain than Orders, so relate it to a Month-level column or use TREATAS. Measures: [Total Sales], [Target Sales], Variance = [Total Sales] - [Target Sales], Achievement % = DIVIDE([Total Sales], [Target Sales]). Visuals: KPI cards per city, a clustered column chart (Actual vs Target), a bullet-style bar with a target line, and a matrix by region → city with conditional formatting on Achievement %. Regions come from the Module 12 groups (West, Vidarbha, …).

M17. The client wants users to switch between Sales, Profit and Quantity. How would you build it?

Reported for: Deloitte [S4] · also PwC [S11]

Use a field parameter (Modeling › New parameter › Fields) containing [Total Sales], [Gross Profit] and [Total Quantity]. Put the parameter on the chart's Y-axis and as a slicer. The older method is a disconnected table with SWITCH(SELECTEDVALUE(...)) (Module 21).

M18. Build a Top N customers report where the user picks N.

Reported for: Deloitte [S4] · also Mu Sigma [S30]

Create a what-if parameter Top N (1–20). Then:

Customer Rank = RANKX(ALLSELECTED(Customer[Customer Name]), [Total Sales], , DESC, Dense)
Show in Top N = IF([Customer Rank] <= [Top N Value], 1, 0)

Add Show in Top N as a visual-level filter (is 1) on a bar chart of customers. Alternatively, a Top N visual filter works when N is fixed.

M19. How do you create and use Power BI templates?

Reported for: Deloitte [S4]

Save as .pbit (File › Export › Power BI template). A template stores queries, the model, measures and report pages but no data. With parameters (for example the Folder Path or Server Name), the user is asked for values when opening it (Module 10.7). Use it to give every city team the same Blinkit report layout for their own data.

M20. What are the different types of filters in Power BI?

Reported for: Deloitte [S1] · also Accenture [S20], EY [S5], LTIMindtree [S27] [S28], Wipro [S17], Genpact [S29], Mu Sigma [S30]

Visual-level, page-level and report-level (all-pages) filters in the Filters pane, drill-through filters, slicers on the canvas, cross-filtering/highlighting from clicking visuals, Top N, relative date and measure filters at the visual level, plus RLS filters applied by security roles, and URL filters in the Service. They combine with AND logic (Module 22.12).

M21. What is incremental refresh? Have you worked on it?

Reported for: Deloitte [S1] · also Capgemini [S22], KPMG [S8], Wipro [S18], Tech Mahindra [S24] [S25], Genpact [S29], Mu Sigma [S30], PwC [S11]

Incremental refresh keeps historical partitions and refreshes only recent data. Steps: create RangeStart and RangeEnd Date/Time parameters, filter the date column with them (the filter should fold to the source), then set the policy on the table (for example store 3 years, refresh the last 10 days). It takes effect after publishing. Prerequisites: a date/time column and ideally a foldable source (Module 10.8). If you haven't used it in production, say so honestly and explain these steps.