Forum Discussion
Relationship Issue Between Year Dimension and Fact Table
Description:
I created a year dimension table with distinct years and linked it to the year column in my fact table. The relationship doesn’t work unless I set the cross-filtering option to "Both." When I choose "Single," no filtering occurs. I suspect the issue is related to a mismatch in granularity between the tables. The fact table contains full dates, while the dimension table only has distinct years. Any suggestions?
Hello Ashish,
Thank you for your helpful response!
I believe the issue might be that when I tried creating the calendar table, the relationship setup window was too large, hiding the "Save" and "Cancel" buttons at the bottom. As a result, I may have mistakenly selected the wrong option while navigating with the Tab key.
Do you happen to have a solution for resizing or adjusting the window so that all options are visible?
I’ll give it another try following your advice.
Thanks again for your guidance!
Best regards,
Youssef Hamid
4 Replies
I use this date table from SQLBI and love it! Once you get this in your model you can join to the date in your fact table and then filter on year from the dim table (and do any of the time intelligence measures you may need). DAX Date Template - SQLBI
Hi,
You should build a Calendar Table with a relationship (Many to One and Single) from the date column of the Fact Table to the Date column of the Calendar Table. In the Calendar Table, create calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number column. To any visual, drag Year from the Calendar Table.
Hope this helps.
Hello Ashish,
Thank you for your helpful response!
I believe the issue might be that when I tried creating the calendar table, the relationship setup window was too large, hiding the "Save" and "Cancel" buttons at the bottom. As a result, I may have mistakenly selected the wrong option while navigating with the Tab key.
Do you happen to have a solution for resizing or adjusting the window so that all options are visible?
I’ll give it another try following your advice.
Thanks again for your guidance!
Best regards,
Youssef HamidYou are welcome. No, i do not.
