Time dependent relationship

[Hanspw]

Hi.

So i have a fact table, which includes a project/product. And then i have a dim table with project/product groups. So this works fine. However i want the flexibility to change the releationsships over time. For example project X is up until 2020 included in project group 1. But from 2021 i want this same project to instead be included in project group 2. And i want to keep the correct history, so that the project will show up in project group 1 for historical data from before 2021, but in project group 2 for data from 2021.

What i would think would be a good solution is that i add two columns in my dim table with from_date and to_date. I could use format YYYYMM. So if to_date is set to 202012 it would only give a match in periods to and before december 2020. 
And then i would add a new row in the dim table with the same project and the new project group, with from_date 202101.

So is the question, how would i establish this time dependtend relationship in my model?

Is it even possible to still keep this as a dim table with relationsship, or do i need to merge the dim table into the fact table to get this working? And if so, what would be the best way to merge the dim table to my fact table, with some sort of "if statement" on year/month? I have tried power query merge before but it made updating the model very slow.... so i hope there is another way?

[Anonymous]

HI @Hanspw,

Perhaps you can add a new table to extract raw table data and add a custom column for calculating the dynamic group number based on the last date.

DateGROUP =
VAR _list =
    ALL ( Table[Date] )
RETURN
    ADDCOLUMNS (
        _list,
        "Group",
            "Group "
                & (
                    MAXX ( _list, YEAR ( [Date] ) ) - YEAR ( [Date] ) + 1
                )
    )

Then you can use this dimension/bridge table to link raw table 'date' and new table 'group'. (new table group mappings will dynamic changes if the last date updated)
Regards,
Xiaoxin Sheng

[Hanspw]

Thank you for your help. This sounds like something i could test. Im just a little uncertain how this tables would link together.

This is my currenct relationsship from "Datagrunnlag" (Fact table) to "Dim_prosjekt" (dimension table for project groups):

In fact table i have a date field for the transactions (lets use Month). And in the dimension table i can add a date_from and date_too column for each project. But lets say i do this, and change the dimensions for lets say project X. Then the project X would be two rows in the dimension table. One row for the active period until 2020 (with dato_too 202012) and one row for the active period from 2021 (with date_from 202101). Then i guess this releationsship no longer will be a many-to-one relationsship, since the project_ID exist two times. So i probably need to connect two IDs together (Project_ID and some kind of date verificator) to get the relationsship a many-to-one again?

The date_verification needs to check the month of each transaction, and then connect to the right row in the dimension table.

This new table you suggest, would this new table be something in between this two existing tables, to activate this date verification field or am i way off?

[Anonymous]

Hi @Hanspw,

It sounds like you already have a date dimension table. If that is the case, you can direct mapping the bridge table 'date' fields to your date dimension table. 

BTW, you did not need to worry about the project and date mapping, it will keeping the filter effect based on date dimension and fact table relationships. 

In fact, power bi is suitable to use 'star schema' to build the relationships instead of 'chain' relationship.

Understand star schema and the importance for Power BI 

Regards,
Xiaoxin Sheng

[Hanspw · Accepted solution]

So i finally figured this out. I add my solution in case anyone wants to know how i solved this.

Thank you @Anonymous for pointing me in the right direction, this article made me realise my problem was a Type 2 Slowly Changing Dimension table:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema#slowly-changing-dimensions

Not easy when im new to Power BI and dont know what all problems are called. Once i figured this out it was much more easy to try and find a solution on google and youtube.

My dimension table is based on an excel spread so it does not have timestamps or version logs. So the solution was indeed to add multiple rows for a project, with two colums for "dato_from" and "date_to". Since project no longer was uniqe ID i made a index column. I then added a column in the fact table (for same index_column_ID), and i had to to a filter lookup to the dim table (based on project_ID, date_from and date_to), to add right index_ID to fact table. 

I tried two versions of this in power query, however when i try to add the column in the fact table with lookup for each row, the data refresh takes longer time than i would like. I finally found a method in dax which avoided that, so it solved my lookup problem and still kept the low refresh time. It is based on this youtube video:

https://www.youtube.com/watch?v=BGcfaROCcto&t

My dax fomula in the end, for adding the index column in the fact table:

dim_prosjekt_index = 
    CALCULATE(
        VALUES(x_LookupTable_z_ark[dim_prosjekt_index]),
        FILTER(x_LookupTable_z_ark,
            Datagrunnlag[dim_prosjekt_ID]=x_LookupTable_z_ark[dim_prosjekt_ID] && 
            Datagrunnlag[transaction_date]>=x_LookupTable_z_ark[date_from] && 
            Datagrunnlag[transaction_date] <= x_LookupTable_z_ark[date_to]
        )
    )

I first tried to make this lookup from the dim_project table, which i want to tie to the fact table with relationship. But it gave me a circular reference. So i had to do the same thing as in the youtube video: i made a copy of the dim_project table (lookuptable), made the lookup to this lookuptable to fint the correct index_ID to add to fact table, and then made the reference to dim_project table.

Problem solved 🙂

[lbendlin]

There's a simpler way. Record the project-to-group relationship on day level granularity in an additional dimension table.

[Hanspw]

Sounds very interesting if there is a easy way to do this. How would this additional dimension table look, and how will the relationsship between the existing fact table and project_group dimension table be, with this new additonal dimension table?