Forum Discussion
Date Table Relationships
Hi everyone
Please bear with me as I'm new to Power BI. I'm having a problem getting a date table and relationships working. The image below shows the realtionships currently setup. I have a dynamic date table with some columns for month, quarter etc. This date table is related to two of my fact tables.
I have then setup a slicer on my page which has the Quarter field from the Dates table on there. However, for some reason only a couple of the records from my fact tables seem to link with the dates table. As shown below, there is a Blank option in the quarter slicer and most of the records appear if that option is clicked:
If I choose Qtr 1 some records appear:
Clicking any of the other quarters displays no records even though there are records there that have dates that should fall under these:
I'm confused because I'm not sure why some records are picked up but most are not. i would have assumed either the relationships were working or not working but I seem to have something in between. I've tried remocing all but one of the relationships and also trying Single and Both cross-filter directions but this makes no difference.
I'd be grateful if anyone who has come across this before could help in any way.
Thanks in advance and if you need more information let me know.
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
7 Replies
- LivioLanzoSolution Sage
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.
- AnonymousNot applicable
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.
- LivioLanzoSolution Sage
could you share your file?
Anonymous
- DalskiRegular Visitor
This is a good tutorial on relationships https://youtu.be/PNdxy0c1Shg