Benny Austin

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

6 comments on “PowerPivot: DAX Expression for Type 2 SCD Lookup

  1. yaniv levy
    May 26, 2015

    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.

    • Benny Austin
      May 27, 2015

      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.

      • yaniv levy
        May 27, 2015

        hi benny , thank you for the answer. great explenation !
        I allow myself to ask another question. I have [income] table of accounts, each account can have multiple instances for one month. how can I find the last occurrence of all the accounts in a month (and then summarize all of these latest occurrences)?

      • Benny Austin
        June 3, 2015

        Yaniv, if you haven’t already figured out use LASTNONBLANK() in DAX. Marco Russo has a good post on this In MDX that would be LastNonEmpty()

  2. Pingback: Kasper de Jonge PowerPivot Blog » Slowly changing dimensions and PowerPivotPowerPivot for Excel 2010: Give Your Data Meaning

  3. prav
    August 12, 2010

    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: Logo

You are commenting using your 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


This entry was posted on August 8, 2010 by in Power BI and tagged , , , , , , .


© Benny Austin.

Get every new post delivered to your Inbox.

Join 291 other followers

%d bloggers like this: