Synthetic keys and associative linking in Power BI

What it does in Qlik

In Qlik, any two tables that share a field name are associated. If they share more than one field, the engine creates a synthetic key table ($Syn 1) that holds the combinations. That is usually a modeling warning sign. Loops (circular references) are broken by making some tables loosely coupled.

Qlik example

Sales:  LOAD Year, CustomerID, Amount FROM [lib://Data/sales.qvd] (qvd);
Budget: LOAD Year, CustomerID, Budget FROM [lib://Data/budget.qvd] (qvd);
// -> $Syn 1 = Year + CustomerID links Sales and Budget

// usual Qlik fix: explicit key
// Year & '|' & CustomerID AS %SalesBudgetKey

Power BI equivalent

Star schema (preferred)

Create Customer and Date (or Year) dimensions. Relate Sales[CustomerID] and Budget[CustomerID] to Customer[CustomerID], and both year columns to the date dimension. Measures from both facts then work together through the shared dimensions, without the fact tables being linked to each other.

Budget variance = [Sales] - [Budget]   -- slices by Customer and Year from the dimensions

Composite key column (when you really need a direct link)

= Table.AddColumn(Sales, "SalesBudgetKey", each Text.From([Year]) & "|" & [CustomerID], type text)

Role-playing dates

-- Orders[ShipDate] -> 'Date'[Date] is an inactive relationship
Sales by ship date = CALCULATE ( [Sales], USERELATIONSHIP ( Orders[ShipDate], 'Date'[Date] ) )

Related pages