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

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!

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
    )