Forum Discussion
Time dependent relationship
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 🙂
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
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?
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 ShengSo 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 🙂