The greatest strength of PowerPivot is it’s ability to create relationship with different sources of data and piggyback on this relationship for various kinds of analysis. PowerPivot relationship requires one-to-many relationship between the parent and child tables. But what happens if the relationship between parent and child tables is many-to-many instead? For e.g. you might want to establish a relationship between a fact table that has a Business Key to the dimension table and that dimension table maintains history. In this post I explain how to establish a relationship between two tables that do not exhibit one-to-many relationship on their Business Key using a technique that is similar to an ETL Type 2 SCD lookup using Data Analysis Expressions (DAX).

For this example, my fact table is [SalesOrderDetail] from AdventureWorks. I would like to establish a relationship between [SalesOrderDetail] and [DimProduct] from the AdventureWorksDW. [DimProduct] table is a type 2 SCD dimension that maintains historical changes to Products. This means that for each Product in [SalesOrderDetail] there could be many date dependant Product records in [DimProduct]. The end game is to associate the fact record with the surrogate key of the [DimProduct] that is relevant to the Product applicable at the [Order Date]

1. The first step is to get the Business Key of the Product. In this example the Business Key is not readily available in the [SalesOrderDetail] table. So it is derived by defining a relationship between [SalesOrderDetail] and [Product] table in AdventureWorks as shown below (Screen Capture 1). Take note that this relationship between the AdventureWorks tables is one–to-many.

2. With the relationship defined, the Product Business Key can be simply derived using the DAX Expression – RELATED(Product[ProductNumber]) (Screen Capture 2)

3. The Product Business Key gives an entry-point to query the [DimProduct] table from AdventureWorks DW. Using the DAX expression given below, every fact record is now linked to its Product Surrogate Key using Product BusinessKey and Order Date thereby paving way to define a relationship between the [SalesOrderDetail] from AdventureWorks and [DimProduct] from the AdventureWorksDW

<br>
=MinX(Filter(DimProduct, DimProduct[ProductAlternateKey]=[ProductBusinessKey] &amp;&amp; ([OrderDate] &gt;= DimProduct[StartDate] &amp;&amp; [OrderDate] &lt;= DimProduct[EndDate])),DimProduct[ProductKey])<br>

The Filter() function in the above DAX expression, pretty much does a Type 2 SCD lookup on the [DimProduct] table using the Product Business Key and the Order Dates and returns a single row of [DimProduct] as a table. The MinX() function works on the table expression returned by the Filter() function to return the Product Surrogate Key.

4. The Product Surrogate Key now has a many-to-one relationship with the [DimProduct] table in AdventureWorksDW and the relationship can now be defined without any ambiguity (Screen Capture 4).

As you can see DAX can be effectively used to facilitate creating of relationship between data sources even though they do not readily exhibit one-to-many relationship between parent and child tables.

Benny Austin