Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

  • 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.

     

     

    • Anonymous's avatar
      Anonymous
      Not 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.