Forum Discussion
Date Table Relationships
Anonymous
IF your analysis will be at the day level then during the ETL process modify this column to extract only the date part without the time
IF you also need to analyze things by time then split the column in two columns, one with the date part and one with the time part and create a time dimension as well
Hello Anonymous !
You have a referential integrity issues, which means that not all the DateKey within your fact tables can be found in the Date dimension table, you may want to check that.
Also I'd turn off the bi-directional filter if I were you and switch it to one direction.
Thanks for the reply LivioLanzo, I've checked both tables and all the dates in my fact tables are present in the date table, along with corresponding quarter information.
I also changed the cross filter direction to single but the issue remains.
could you share your file?
Anonymous
LivioLanzoI think I've found the issue although I'm not sure how to solve it. I had the date columns in my fact and date tables formatted as Date in Power BI. However, if I set them to Date Time most of the records in my fact tables have different times on them:
This now makes sense why in the above table, only the 22/03/2018 is mapping to the date table, as this is the only one with a time of 00:00:00 which matches the date table. The one thing I'm confused about now though is why there are different times on these tables? The data comes from SharePoint Online lists and the fields in these lists only allow a user to select the date. It doesn't seem to matter how I format the data in Power BI, it still doesn't match up because of this time discrepancy.
Anonymous
IF your analysis will be at the day level then during the ETL process modify this column to extract only the date part without the time
IF you also need to analyze things by time then split the column in two columns, one with the date part and one with the time part and create a time dimension as well