Help me with this SCD type 2 issue. I am a beginner.

[Rahul_Nair]

Hi everyone,

I am implementing SCD Type 2 in Power BI and stuck on one specific filtering problem. Would really appreciate any help.

My data model has three tables. First is a Fact Table with columns TransactionDate, DimensionID, Amount and SK which is a Surrogate Key added via Power Query merge. Second is a Dimension Table called DIM_Records with columns SK which is a unique Index column, DimensionID, Description, ValidFrom and ValidTo both Date type. Third is a Calendar Table called DIM_Calendar with a Date column.

Current relationships are Fact SK to DIM_Records SK as Many to One and Active. DIM_Calendar Date to Fact TransactionDate as Many to One and currently Inactive. There is no direct relationship between DIM_Calendar and DIM_Records.

What I did in Power Query is first added an Index column called SK to DIM_Records. Then merged DIM_Records into Fact table on DimensionID. Then expanded SK, ValidFrom and ValidTo from the merge. Then added a custom column to filter where Fact TransactionDate is greater than or equal to ValidFrom and less than or equal to ValidTo. Then kept only correctly matched rows. Then deleted ValidFrom and ValidTo from Fact table. Then built the Fact SK to DIM_Records SK relationship.

What I want is a table visual showing DimensionID, Description, ValidFrom and ValidTo. When I filter DIM_Calendar Date to is on or before March 31 2020 I want to see only the dimension row whose validity period covers that date.

For example DIM_Records has two rows for ID001. Row 1 has ValidFrom 01.01.1980 and ValidTo 31.03.2020. Row 2 has ValidFrom 01.04.2020 and ValidTo 31.12.9999. When I filter Date is on or before 31.03.2020 I expect only Row 1 to show. When I filter Date is after 31.03.2020 I expect only Row 2 to show.
What I tried so far is the following. First I tried creating a direct relationship from DIM_Calendar to DIM_Records via ValidFrom but this gives an ambiguous error because Calendar to Fact relationship already exists. Second I tried making Calendar to Fact inactive and creating Calendar to DIM_Records via ValidFrom but this only handles one column and not the full date range.

Third I tried multiple DAX measures.
First measure using ALLSELECTED approach. Is Valid Period = VAR RangeStart = MIN(‘DIM_Calendar’[Date]) VAR RangeEnd = MAX(‘DIM_Calendar’[Date]) RETURN IF(AND(‘DIM_Records’[ValidFrom] <= RangeEnd, ‘DIM_Records’[ValidTo] >= RangeStart), 1, 0)

Second measure using CALCULATETABLE approach. Is Valid Period = VAR CurrentDate = CALCULATETABLE(VALUES(‘DIM_Calendar’[Date]), ALLSELECTED(‘DIM_Calendar’)) VAR RangeStart = MINX(CurrentDate, ‘DIM_Calendar’[Date]) VAR RangeEnd = MAXX(CurrentDate, ‘DIM_Calendar’[Date]) RETURN IF(ISBLANK(RangeEnd), 1, IF(AND(‘DIM_Records’[ValidFrom] <= RangeEnd, ‘DIM_Records’[ValidTo] >= RangeStart), 1, 0))

None of these measures are giving the correct filtering result.

My question is what is the correct way to filter a dimension table with validity periods ValidFrom and ValidTo using a Calendar table date filter in Power BI? Is there a correct DAX measure or data model approach that handles this SCD Type 2 filtering correctly?

Any help is greatly appreciated. Thank you!

[grazitti_sapna · Accepted solution]

Hi @Rahul_Nair,

Instead of Using calculatetable or allselected, you can use SelectedValue function.

Attached is the working power BI solution for you.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[burakkaragoz · Accepted solution]

Hi @Rahul_Nair ,
Handling Slowly Changing Dimensions (SCD Type 2) can be tricky at first, but your data model setup with the Surrogate Key (SK) is actually perfect!

As @grazitti_sapna mentioned, the issue with your DAX is that using ALLSELECTED and CALCULATETABLE overcomplicates the row context. When you put a measure into a table visual that already has DIM_Records columns, you just need to check the current row's validity against the calendar selection using SELECTEDVALUE.

Here is the exact DAX measure you need to solve this.

The Solution: The DAX Filter Measure

Create this measure and add it to your Table Visual's "Filters on this visual" pane, then set the condition to "is 1".

 

Is Valid Record =
VAR SelectedDate = MAX('DIM_Calendar'[Date])
VAR CurrentValidFrom = SELECTEDVALUE('DIM_Records'[ValidFrom])
VAR CurrentValidTo = SELECTEDVALUE('DIM_Records'[ValidTo])

RETURN
    IF (
        CurrentValidFrom <= SelectedDate &&
        CurrentValidTo >= SelectedDate,
        1,
        0
    )

Why this works:

• Your DIM_Calendar is disconnected, which is exactly the correct approach for this specific filtering requirement.

• When you use the Date slicer (for example, "on or before 31.03.2020"), MAX grabs that exact upper limit date.

• The measure then looks at every single row in your table visual. If the SelectedDate falls between that row's ValidFrom and ValidTo dates, it returns 1 (showing the row).

• This perfectly handles your ID001 example, showing only the active row for the date you slice.

  Just make sure your table visual includes the SK or DimensionID so SELECTEDVALUE has a single row to evaluate!

  If this solves your filtering issue, please consider giving a Kudo and marking it as the Accepted Solution! ✅

  This response was assisted by AI for translation and formatting purposes.

[Rahul_Nair]

@burakkaragoz Thanks for the solution. It's working. Now, I got another issue. I have the amount column in fact table. I created a message for the amount column as Amount SCD = SUM('FACT_Cost_centers'[Amount]). But no data is not showing in the table visual and only the headers (column names) is showing, the moment I am selecting the measure to show in the visual. Do you have any idea?

[grazitti_sapna · Accepted solution]

Hi @Rahul_Nair,

Instead of Using calculatetable or allselected, you can use SelectedValue function.

Attached is the working power BI solution for you.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Rahul_Nair]

Hey @grazitti_sapna @thanks for the idea. I was not able to open the file you shared. But the idea of using Selectedvalue helped me to find the solution 😄

[grazitti_sapna]

@Rahul_Nair , If it helped, Kindly mark as solution