SCD Type 4 - Data Modelling

[santhidhanuskod]

Hi,

I have implemented SCD Type 4 and loaded data in 2 diff tables, live and history. WE have lots of tables and all of them are implemented with type 4. and we will definitely have many-many relationships when we model this in power bi. WE have active startdte and activeenddte in both tables. live wil have null vlaues for all records for activeenddaate column. Histor table will contain the actual end date for that column.

Header column is unique in these tables.

As we maintain data datewise, there will be duplicates in header column. We can identify the unique records only with the combination of header and recorddate. 

Business requirement is to select the activestartdate in the slicer in the report and they should be able to see the data for that particular date.

how can I model in power bi, how to get rid of many-many issue? Also the challenge is to select records for that particular date is - We need to fetch records where date falls between activestartdate and activeenddate. I am finding difficulty in modelling this. Please help me with the approach.

[v-pgoloju]

Hi @santhidhanuskod,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[maruthisp]

Hi @santhidhanuskod , 
As per the suggestion/solution from DataNinja777, I tried to come up with the data modeling solution that might give you an idea how to handle the SCD Type 4 data without many-to-many relationships.

SCD Type 4 - Data Modelling.pbix

This is an interesting scenario for me as well and good learning to solve this kind of data modleing topics.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[v-pgoloju]

Hi @santhidhanuskod,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @santhidhanuskod,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[santhidhanuskod]

Thanks. Let us try to implement this and provide an update.

[DataNinja777 · Accepted solution]

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,