SCD Type 4 - Data Modelling

Hi santhidhanuskod ,

To model SCD Type 4 in Power BI without running into many-to-many relationship issues, the best approach is to combine the Live and History tables into a single table. In Power Query, you can standardize the Live table by replacing null values in the ActiveEndDate column with a placeholder like 9999-12-31, which will act as the open-ended period for currently active records. Then, you can merge the Live and History tables using the Table.Combine function in Power Query:

let
    LiveWithEnd = Table.ReplaceValue(Live, null, #date(9999,12,31), Replacer.ReplaceValue, {"ActiveEndDate"}),
    Combined = Table.Combine({LiveWithEnd, History})
in
    Combined

Once you have the unified Combined table, create a separate Date table using DAX. This Date table should span from the minimum ActiveStartDate to the maximum ActiveEndDate in your data:

DateTable = CALENDAR(MIN(Combined[ActiveStartDate]), MAX(Combined[ActiveEndDate]))

Do not create a relationship between the DateTable and the Combined table. Instead, use a slicer on the DateTable[Date] column. To filter records that were active on the selected date, create a measure that checks whether the selected date falls between each row’s ActiveStartDate and ActiveEndDate. This logic should be written in a measure like the following:

ShowActiveRecords :=
CALCULATE(
    [SomeMeasure],
    FILTER(
        Combined,
        SELECTEDVALUE(DateTable[Date]) >= Combined[ActiveStartDate]
            && SELECTEDVALUE(DateTable[Date]) <= Combined[ActiveEndDate]
    )
)

Replace [SomeMeasure] with an actual metric such as COUNTROWS(Combined) or a specific aggregation. This measure will dynamically filter the Combined table to return only the records that were valid (active) on the selected date, based on the user's slicer input. This method avoids direct relationships that could lead to ambiguous many-to-many joins, while still delivering accurate results based on the time window defined by your SCD Type 4 setup.

Best regards,