What Is Slowly Changing Dimension in Power BI?

Tracking changes in your data over time is fundamental for accurate reporting, but it’s often easier said than done. When an employee switches departments or a product category is renamed, your historical reports can become instantly misleading. This article explores how to solve this common analytics challenge using Slowly Changing Dimensions (SCDs) in Power BI.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is a Slowly Changing Dimension?

In data modeling, a "dimension" is a table that contains descriptive, categorical data. Think of tables for products, customers, employees, or store locations. They describe the "who, what, when, and where" behind your business events. An event, like a sale, is known as a "fact."

A "Slowly Changing Dimension" is a dimension where the attributes change over time - but, as the name suggests, not frequently. For instance:

• An employee changes their marital status or moves to a new sales territory.
• A customer's address or company name is updated.
• A product is moved from one category to another.

These aren't daily transactional changes, they're occasional updates to descriptive attributes. The challenge is deciding how to handle these changes in your data model. If you simply overwrite the old value with the new one, you lose historical context. For example, if you run a Q1 sales report by territory after a salesperson has moved to a new territory in Q2, their Q1 sales might be incorrectly attributed to their new location. SCDs provide structured methods to manage this history, ensuring your reports remain accurate representations of the past.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Most Common Types of SCDs

There are several established methods for handling data changes in dimension tables. The one you choose depends on your business requirements for historical data. Let's look at the most common types.

SCD Type 0: The Fixed Dimension

This is the "do nothing" approach. The dimension attributes are considered fixed and will never change. This is typically used for attributes that are constant by nature.

• How it works: Any changes to attribute values are considered errors and are corrected directly. No history is kept.
• Example: A person's date of birth or a company's founding date. These values shouldn't change, so Type 0 is perfectly suitable.
• When to use it: When you are certain an attribute value will not change for the lifetime of the dimension record.

SCD Type 1: Overwrite Old Value

SCD Type 1 is the simplest method for handling changes: you just replace the old value with the new one. This approach does not preserve any historical information. You only ever see the most current state of the data.

• How it works: Updates to a dimension record overwrite the existing data in the table.
• Example: A customer misspelled their name during signup ("Jann Do") and wants it corrected to "Jane Doe." In this case, you don't need to keep a record of the typo, so overwriting it is the best course of action.
• Downside: This method completely erases historical context. If Sales Rep Sarah was in the 'North' region last year and moved to 'West' this year, a Type 1 update would change her region to 'West'. A report on last year's sales by region would now show all her past sales under the 'West' region, which is incorrect.

SCD Type 2: Add a New Row for Historical Tracking

This is the most common and powerful method for maintaining a full historical record. Instead of overwriting a changed record, you add a new row containing the updated information. The old record is preserved but marked as outdated.

• How it works: To make this happen, the dimension table must include additional columns to manage the history, such as:

Let's look at an example. Imagine our employee, Jane Doe, with EmployeeID E123, moves from the Sales to the Marketing department on July 1, 2023.

Original Employee Table (before change):

Updated Employee Table (after change):

Now, any sales Jane made before July 1, 2023, can be accurately linked to her time in the Sales department, and any after that date can be linked to the Marketing department.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

SCD Type 3: Add a New Column

SCD Type 3 provides a more limited history by adding a new column to the dimension table to store a previous value. You might have columns like CurrentDepartment and PreviousDepartment.

• How it works: When a change happens, the value in CurrentDepartment is copied to PreviousDepartment, and CurrentDepartment is updated with the new value.
• Example: When Jane moved from Sales to Marketing, the record for E123 would be updated to show CurrentDepartment = 'Marketing' and PreviousDepartment = 'Sales'.
• Downside: This method can only track the most recent change. If Jane moved from Operations to Sales and then to Marketing, you would lose the record of her time in Operations. It's best suited for analyses where comparing the current state to the single, most recent previous state is all that's required.

Implementing SCD Type 2 Logic in Power BI

While robust ETL (Extract, Transform, Load) processes in a data warehouse are the ideal place to manage SCD logic, you can replicate this functionality within Power BI using Power Query. This is helpful when you don't have a dedicated data warehouse and pull data directly from source systems that only provide the current snapshot of data.

The general objective is to take new source data and use it to update an existing dimension table where you've been maintaining historical records. Here’s a conceptual overview of the steps in Power Query:

1. Load Your Data Sources: You will need two primary queries - one for your new source data that shows the current state (e.g., NewEmployees) and one for your existing historical dimension table (e.g., DimEmployee).
2. Identify Changed Records: The goal here is to find employees whose tracked attribute (e.g., Department) has changed. You can do this by merging DimEmployee (filtered for only IsCurrent = TRUE records) with NewEmployees on the business key (EmployeeID). Any records where the Department from DimEmployee does not match the Department from NewEmployees are your changed records.
3. Identify Brand New Records: To find completely new employees, you can perform a merge with a "left anti" join from NewEmployees to DimEmployee on the EmployeeID. The rows that remain in the NewEmployees table are ones that do not exist in your dimension table yet.
4. Build the Final Table by Appending Unions:
5. Append & Load: Finally, combine these four prepared sets of data into one single, updated dimension table and load it into your Power BI data model. You can then write a process to save this result and use it as the DimEmployee source for the next data refresh.

Reporting with SCD Type 2 Data

With a properly structured SCD Type 2 dimension, historical reporting becomes much more reliable. In your Power BI data model, you connect your fact table (like Sales) to your dimension table (DimEmployee) using the surrogate key (EmployeeSK), not the business key (EmployeeID).

When the data is loaded into the data warehouse or processed by your ETL logic, each fact record (e.g., each individual sales transaction) must be assigned the correct surrogate key that corresponds to the state of the dimension at the time of the transaction. For instance, a sale Jane Doe made on March 15th, 2023, must be assigned the surrogate key for her record when she was in the Sales department (EmployeeSK = 101).

If this connection is made correctly, your reports in Power BI just work. You can create a table or chart showing Sales Amount by Department, drop it on the canvas, and Power BI will automatically attribute sales to the correct historical department based on the EmployeeSK relationship. No complex DAX is needed to handle the shifting history at the report level because the hard work was already done during the data transformation stage.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Final Thoughts

Slowly Changing Dimensions solve a critical problem in data analytics by preserving historical context and ensuring your reports are accurate over time. While setting up the logic, especially for SCD Type 2, requires some foundational work, the payoff is immense, preventing skewed data that could lead to poor business decisions.

Managing this kind of historization and data transformation logic in Power Query or with spreadsheets can become cumbersome and error-prone as your data grows. With Graphed, we automate the complexities of data preparation. Instead of building manual ETL processes, you simply connect your data sources, and our AI-powered analyst handles the cleaning, modeling, and historization automatically, delivering insights that are both current and historically accurate. You can ask straightforward questions like, "Compare sales revenue by employee territory over the last two years," and get a correct visualization instantly, without worrying about whether you applied your SCD logic correctly.