Forum Discussion
Data Model with SCD type 4 implementation
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 tpe 4. and we will defintely have many-many relationships when we model this in power bi.
As we maintain data datewise, there will be duplicates in the certain columns. For eg I have a header column which is duplicated bec of type 4 implementation. We can identify the unique records only with the combination of header and recorddate.
Business requirement is to select the recorddate in the report and they should be able to see the data for tht particular date.
how cn I model in power bi, how to get rid of man-many issue?
Hello santhidhanuskod,
Can you please try this approach:
1. Create a Date Dimension Table
DateTable = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), TODAY()), "Year", YEAR([Date]), "Month", FORMAT([Date], "MMM"), "Quarter", "Q" & FORMAT([Date], "Q"), "Year-Month", FORMAT([Date], "YYYY-MM") )2. Create a Bridge Table
BridgeTable = DISTINCT( UNION( SELECTCOLUMNS( LiveTable, "Header", LiveTable[Header], "RecordDate", LiveTable[RecordDate]), SELECTCOLUMNS( HistoryTable, "Header", HistoryTable[Header], "RecordDate", HistoryTable[RecordDate]) ) )Usually, creating a bridgiing table is a general solution for many-to-many relationships.
Many-to-many relationship guidance - Power BI | Microsoft Learn
Connecting Fact Tables in Microsoft Fabric: A Brid... - Microsoft Fabric Community
Or you can use the Merge queries to merge tables with the same key value in the Power query.
Merge queries overview - Power Query | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hello santhidhanuskod,
Can you please try this approach:
1. Create a Date Dimension Table
DateTable = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), TODAY()), "Year", YEAR([Date]), "Month", FORMAT([Date], "MMM"), "Quarter", "Q" & FORMAT([Date], "Q"), "Year-Month", FORMAT([Date], "YYYY-MM") )2. Create a Bridge Table
BridgeTable = DISTINCT( UNION( SELECTCOLUMNS( LiveTable, "Header", LiveTable[Header], "RecordDate", LiveTable[RecordDate]), SELECTCOLUMNS( HistoryTable, "Header", HistoryTable[Header], "RecordDate", HistoryTable[RecordDate]) ) )Usually, creating a bridgiing table is a general solution for many-to-many relationships.
Many-to-many relationship guidance - Power BI | Microsoft Learn
Connecting Fact Tables in Microsoft Fabric: A Brid... - Microsoft Fabric Community
Or you can use the Merge queries to merge tables with the same key value in the Power query.
Merge queries overview - Power Query | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.