31.4 KPMG

M33. What is a factless fact table?

Reported for: KPMG [S8]

A fact table with no numeric measures. It only records that an event happened, or which combinations exist. Example: a Store Promotion table (Store ID, Product ID, Date) showing which products were on offer in which dark store on which day. You analyse it by counting rows, for example "products on promotion that sold zero units".

M34. Give an example of a slowly changing dimension (SCD).

Reported for: KPMG [S8]

A dimension whose attributes change over time. Example: dark store BLK-NSK-02 moves from the North Maharashtra region to a new Nashik Metro region. Type 1 overwrites the old value (history is lost). Type 2 adds a new row with a new surrogate key and valid-from/valid-to dates, so old orders stay under the old region. Type 2 is usually built in the data warehouse, not in Power BI.

M35. What are relationship modifiers?

Reported for: KPMG [S8]

DAX functions used inside CALCULATE that change how relationships behave for one calculation: USERELATIONSHIP activates an inactive relationship (Orders[Delivered Date] → Date), and CROSSFILTER changes the filter direction (Both, OneWay, None). TREATAS creates a virtual relationship.

Sales by Delivered Date =
CALCULATE([Total Sales], USERELATIONSHIP(Orders[Delivered Date], 'Date'[Date]))

M36. RELATED vs LOOKUPVALUE?

Reported for: KPMG [S8] · also Infosys [S16] (RELATED vs RELATEDTABLE)

RELATED fetches a value from the one side of an existing relationship, in row context (RELATED(Product[Category]) in Orders). LOOKUPVALUE needs no relationship: it searches a table by one or more column/value pairs, and it errors if several different values match. RELATEDTABLE goes the other way (one → many) and returns a table, for example COUNTROWS(RELATEDTABLE(Orders)) in DarkStore. Prefer relationships + RELATED because they are faster and clearer.

M37. What error do you get with many-to-many, and what are the limitations of DirectQuery?

Reported for: KPMG [S8] · also Genpact [S29]

If you try a one-to-many relationship when both columns have duplicates, Power BI refuses with a message that the cardinality isn't valid, because the column on the "one" side must have unique values. You can then choose many-to-many, which shows a warning. DirectQuery limitations: speed depends on the source, every interaction sends queries, some DAX and Power Query transformations are limited, there is a 1 million-row limit on intermediate results, there are no calculated tables from DirectQuery data in the classic mode, and the source needs capacity for many users.

M38. How do you change the order of values on a column chart's X-axis?

Reported for: KPMG [S8] · also Capgemini [S22]

Use … › Sort axis for sorting by value or name. For a custom order (Mon, Tue, … or delivery-time bins "0–10", "10–20", "20+"), create a sort column (Day No, Bin Order) and use Column tools › Sort by column. In DAX, a calculated column is one way to build it:

Bin Order = SWITCH(Orders[Delivery Bin], "0-10", 1, "10-20", 2, "20+", 3)

M39. What is a surrogate key and why use it?

Reported for: KPMG [S8]

A system-generated key (usually an integer) with no business meaning, used instead of the natural key (like "BLK-PUN-01"). Benefits: integer keys are small and compress well, they handle SCD Type 2 history, and they join data from sources whose natural keys clash. In Power Query you can create one with an Index column in the dimension and then merge it into the fact table.

M40. What is the difference between ALL, ALLSELECTED and ALLEXCEPT? Show with DAX.

Reported for: KPMG [S9] · also Accenture [S20], PwC [S11], LTIMindtree [S27] [S28] (ALL), Capgemini [S23]

% of All Cities      = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(DarkStore[City])))
% of Selected Cities = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(DarkStore[City])))
Sales Keep Only City = CALCULATE([Total Sales], ALLEXCEPT(DarkStore, DarkStore[City]))

ALL removes filters from the column or table (ignores slicers too). ALLSELECTED removes filters coming from inside the visual but keeps the outer slicer selection, so percentages add up to 100% of what the user selected. ALLEXCEPT removes all filters on a table except the listed columns. REMOVEFILTERS is an easier-to-read alias of ALL when it is used as a CALCULATE filter.

M41. SQL: RANK vs DENSE_RANK (and ROW_NUMBER).

Reported for: KPMG [S9] [S10] · also TCS [S12], Tech Mahindra [S24]

For sales 500, 400, 400, 300: ROW_NUMBER gives 1, 2, 3, 4 (always unique); RANK gives 1, 2, 2, 4 (gaps after ties); DENSE_RANK gives 1, 2, 2, 3 (no gaps). Use DENSE_RANK for "top 3 distinct values" and ROW_NUMBER for de-duplication.

M42. How do you calculate the last 7 months' sales?

Reported for: KPMG [S10]

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

If "last 7 months" must mean complete months before the current one, first shift the end date to the end of the previous month with EOMONTH(MAX('Date'[Date]), -1).

M43. How do you refresh only a single table?

Reported for: KPMG [S10]

In Desktop: right-click the table in the Data pane › Refresh data. In the Service, a scheduled refresh processes the whole semantic model. Table-level (or partition-level) refresh is possible on Premium/Fabric capacity through the XMLA endpoint (for example with SSMS or Tabular Editor) or the enhanced refresh REST API. Tables that don't need refreshing can also have Include in report refresh turned off in Power Query.

M44. How do you keep the same slicer selection across all pages, and which DAX checks a slicer selection?

Reported for: KPMG [S10]

Use View › Sync slicers and tick Sync (and Visible where needed) for each page (Module 22.9). DAX checks: ISFILTERED(DarkStore[City]) (a direct filter on the column), HASONEVALUE(DarkStore[City]) (exactly one value), SELECTEDVALUE(DarkStore[City], "All cities") (the value or a default), and ISCROSSFILTERED (filtered directly or indirectly).

M45. Show sales for the selected country, otherwise a default country.

Reported for: KPMG [S10]

Our version: the selected city, otherwise Pune.

Sales Selected or Pune =
VAR c = SELECTEDVALUE(DarkStore[City], "Pune")
RETURN CALCULATE([Total Sales], DarkStore[City] = c)

With no selection or multiple selections, SELECTEDVALUE returns the default "Pune". If multi-select should add up the selected cities, test ISFILTERED instead.