Forum Discussion
date table not working
My relationship between the date table and my data is not working. The date table is: marked as date table, and i have tried changing the data type to BOTH "date" and "date/time". the dates from my data are also marked as "date" for data type. this seems to be the fix for everyone else but not on my end. Does anyne else have any ways to trouble shoot?
alisonpappas , The table where you are joining the date from the date table. Check if that date have timestamp.
Let us say you are joining to start date of table1, check that the start date has timestamp or not. Best way to check is to change its data type to DateTime and choose a format that that has time and see do see any time other than 12:00 AM , if yes create a new date column
New Date = [Start Date].date
Or
New date = date(year([Start Date]),month([Start Date]),day([Start Date]))
6 Replies
Hi alisonpappas ,
First, you have to make sure that the data type of the reference fields in the two tables that create the relationship are the same. At the same time, the values of the two reference fields need to contain the same values, so that the data will display when the fields in the two tables be placed on the visual at the same time. Could you please provide some sample data of these two tables and the screenshot of Model tab?
You can review the following documentations about how to create and manage relationship.
Understanding relationships in Power BI Desktop
Model relationships in Power BI Desktop
Best Regards
Rena
alisonpappas , The table where you are joining the date from the date table. Check if that date have timestamp.
Let us say you are joining to start date of table1, check that the start date has timestamp or not. Best way to check is to change its data type to DateTime and choose a format that that has time and see do see any time other than 12:00 AM , if yes create a new date column
New Date = [Start Date].date
Or
New date = date(year([Start Date]),month([Start Date]),day([Start Date]))
amitchandak this was it! it came in as 7pm so making a new column changed to 12am and matched thank you all!
Date tables need the key column to be a Date, not a Datetime.
Consequently, the column you want to link it to on the other side has to be a Date column type too.
Your relationship is going FROM the dates table TO the other table as 1:x, right?
All,
The table is a DATE TABLE with a DATE DATA TYPE matched to my data table with a DATE DATA TYPE. It goes from DATA TABLE to DATE because power bi auto makes it go that way when i save.
Hi alisonpappas ,
In your date table i see it DATE data-type, but format is Sunday, January 26, 2020.
Wheras in your data table, it is DATE data-type, but format is April 17, 2020.
CAn you modify the date table format similar to that in your data table?
Thanks,
Pragati
