Forum Discussion
Relationship with date table showing no data
Hi, I feel like I've searched everywhere and just can't find the solution to my issue here.
I am trying to make a relationship between a table I made with DirectQuery from a local SQL server and a Date Table that I made with CALENDARAUTO, and I need to make a visual that shows TotalExtended by month. However, when I try to make a visual it returns no data, like so:
I set the relationship between DateAdded and Date, and it seems to be an incomplete relationship for some reason but I'm not sure if this matters.
Another thing that I think may be the cause is that in the "edit relationship" window it only shows three rows, and the rows it shows are not actually related.
Our data in the table technically goes back to 2015, but I have in the WHERE clause of the DirectQuery to only return data from the past 12 months, so I'm not sure if this could also be causing problems.
If anyone has any ideas I would be very grateful, thank you!
kwil , A date joining with date of the date table should not have a timestamp. So you should bring a date from DB not having timestamp for join
Also consider date table using calendar function if needed
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
3 Replies
- amitchandak
Super User
kwil , A date joining with date of the date table should not have a timestamp. So you should bring a date from DB not having timestamp for join
Also consider date table using calendar function if needed
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
