31.6 TCS

M50. What is data modelling in Power BI? Can two fact tables be linked to each other?

Reported for: TCS [S12]

Data modelling means designing tables, relationships, keys and measures so that filters flow correctly. It is usually a star schema. Two fact tables (Blinkit Orders and Store Targets) should not be related directly. Relate both to shared dimensions (Date, DarkStore/City, Product) and compare them with measures. Linking facts directly causes many-to-many joins and ambiguous paths.

M51. What is Power BI Report Builder, and does it have data modelling?

Reported for: TCS [S12]

Power BI Report Builder is a free desktop tool for paginated reports (.rdl), which are pixel-perfect, printable reports such as invoices or a store-wise daily sales statement that runs to many pages. It uses datasets defined by queries and parameters. It does not have the Power BI Desktop modelling experience (no relationships or DAX measures authored like in Desktop), but it can use a published Power BI semantic model as a data source.

M52. Calculate a rolling 12-month average, with and without a Date table.

Reported for: TCS [S12] · also TCS [S14] (3-month rolling average), Capgemini [S23]

-- with a Date table (recommended)
Rolling 12M Avg Monthly Sales =
VAR win = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
RETURN AVERAGEX(
    CALCULATETABLE(VALUES('Date'[Year Month]), win),
    CALCULATE([Total Sales]))

-- without a Date table: filter the fact table's own date column
Rolling 12M Sales (no Date table) =
VAR maxD = MAX(Orders[Order Date])
RETURN CALCULATE([Total Sales],
    FILTER(ALL(Orders[Order Date]),
        Orders[Order Date] > EDATE(maxD, -12) && Orders[Order Date] <= maxD))

Time intelligence functions need a proper Date table, so explain why the first version is preferred.

M53. What is the model size limit for Pro, PPU and Premium?

Reported for: TCS [S12] · also Genpact [S29] (file over 10 GB)

At the time of writing, Microsoft documents about 1 GB per semantic model with Pro, much larger limits with Premium Per User (around 100 GB) and capacity-dependent limits on Premium/Fabric capacities with the large semantic model format enabled. Limits change, so say "I would confirm on Microsoft Learn". To stay under limits: remove columns, reduce cardinality, aggregate, and use incremental refresh or DirectQuery/composite models.

M54. SQL scenario: customers who visited and bought vs visited but did not buy.

Reported for: TCS [S12]

Our version: an AppVisits table (CustomerID, VisitDate) and the Orders table.

-- visited and ordered
SELECT DISTINCT v.CustomerID FROM dbo.AppVisits v
JOIN dbo.Orders o ON o.CustomerID = v.CustomerID;

-- visited but never ordered
SELECT DISTINCT v.CustomerID FROM dbo.AppVisits v
LEFT JOIN dbo.Orders o ON o.CustomerID = v.CustomerID
WHERE o.CustomerID IS NULL;

In Power BI: a Left Anti merge (AppVisits vs Orders) gives the "visited, no order" list. A DAX alternative: COUNTROWS(FILTER(VALUES(AppVisits[Customer ID]), ISBLANK([Total Orders]))).

M55. What is the difference between a CTE and a view?

Reported for: TCS [S12] · also Tech Mahindra [S24] (CTE vs subquery)

A CTE (WITH x AS (…)) exists only for the single query that follows it. It makes long queries readable and allows recursion. A view is saved in the database, reusable by many queries and users, and can have permissions. Compared with a subquery, a CTE is easier to read and can be referenced more than once in the same statement.

M56. Populate each customer's previous order date.

Reported for: TCS [S14]

As a calculated column in a customer-level orders table (one row per order):

Previous Order Date =
VAR cust = OrderHeader[Customer ID]
VAR d    = OrderHeader[Order Date]
RETURN CALCULATE(MAX(OrderHeader[Order Date]),
    FILTER(ALL(OrderHeader),
        OrderHeader[Customer ID] = cust && OrderHeader[Order Date] < d))

Variables replace the older EARLIER function. In SQL, LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) does the same thing.

M57. Rank products by sales within each category, dynamically.

Reported for: TCS [S14]

Rank in Category = RANKX(ALL(Product[Product Name]), [Total Sales], , DESC, Dense)

In a matrix with Category → Product, ALL removes only the Product Name filter and keeps the Category filter, so ranks restart in each category and respond to slicers such as City and Month. Wrap it in IF(ISINSCOPE(Product[Product Name]), …) to hide the rank on category subtotal rows.

M58. How would you handle missing dates so that time intelligence is correct?

Reported for: TCS [S14] · also Tech Mahindra [S25] (YTD with missing dates)

Never use the fact table's date column for time intelligence. Build a continuous Date table (CALENDAR or Power Query) covering whole years, mark it as a date table, and relate it to Orders[Order Date]. Days without orders (for example a store closed for a festival) still exist in the Date table, so YTD, MoM and rolling totals work. To show 0 instead of blank, use [Total Sales] + 0 carefully, because it can show every date and product combination.

M59. What are the challenges in data refresh, and how do you handle them?

Reported for: TCS [S13]

Typical problems: expired credentials, the gateway offline, a changed source schema (a renamed column breaks a step), timeouts on big tables, privacy-level errors when combining sources, and files renamed or moved. Handling: refresh failure notifications, a service account for credentials, a gateway cluster, incremental refresh, stable views in SQL, and keeping source files in SharePoint with fixed names or using the Folder pattern (Module 9).