PowerPivot: DAX Expression for Type 2 SCD Lookup

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.

Screen Capture 1 - One-to-Many Relationship
Screen Capture 1 – One-to-Many Relationship

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

Screen Capture 2 - Product Business Key Using RELATED()
Screen Capture 2 – Product Business Key Using RELATED()

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

=MinX(Filter(DimProduct, DimProduct[ProductAlternateKey]=[ProductBusinessKey] && ([OrderDate] >= DimProduct[StartDate] && [OrderDate] <= DimProduct[EndDate])),DimProduct[ProductKey])
Screen Capture 3 - Type 2 SCD lookup Using DAX  Expression
Screen Capture 3 – Type 2 SCD lookup Using DAX Expression

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).

Screen Capture 4 -  Relationship
Screen Capture 4 – Relationship

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


  1. hi benny, thanks for the article , i just dont understand the first step – Screen Capture 1 – One-to-Many Relationship. i know that in [SalesOrderDetail] can be many Products becouse there can be many sales for a single Product , and i also know that in [Product] can be many Products becouse there can be many changes throughout history for a single Product.
    in that case the Relationship is : MANY to MANY .
    Many thanks.

    • Yaniv, I think you probably got mixed up between sales and sales detail. The relationship between sales and products is many-to-may as you rightly mentioned. However it is one-to-many between the sales line item/SalesOrderDetail and product.

  2. […] There is more to it like different types and such which you can read all about in PowerPivot. I could make and example myself but there are two sources that already did it for me, one is Marco Russen and Alberto Ferrari’s book  PowerPivot for Excel 2010: Give Your Data Meaning (chapter 7) or see this other solution on this blog post: https://bennyaustin.wordpress.com/2010/08/08/powerpivot-dax-expression-for-type-2-scd-lookup/ […]

  3. The analysis of parent and child many to many relationship is useful while pulling the data from different tables with different level relationship to maintain the granularity level of data.
    This post is really great for data analysis.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s