Forum Discussion
Understanding Date Table and How It Filters
Hi Txtcher ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
Yes, the best and most efficient practice is to add a new column to your history table that contains only the date (without the time) and use that for your relationship to the calendar table.
This allows you to:
-
Maintain the original date/time values for analysis that needs time-level detail.
-
Use your calendar table (which only includes dates) effectively in slicers and visuals without needing a massive minute-by-minute calendar.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
-
Hi Txtcher,
The most common reason your Calendar slicer isn’t “bringing back” all 100 000+ rows from your History table is that the two date fields don’t actually match, even though they look the same on the surface. Because,
History[CreateDate] is a true Date/Time (with hours, minutes, seconds),
Calendar[Date] is a Date only (midnight / 00:00:00)
When you build a relationship in Power BI between a Date/Time field and a pure Date field, the engine only “joins” on exact DateTime matches. So if only two rows in your history happen to have a CreateDate of, say, “2025-05-01 00:00:00,” you’ll only ever see those two rows when you filter by that date.
Also, try to go through the below link to understand more context:
Solved: why is my date filter is not filtering correctly? - Microsoft Fabric Community
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
Thank you so much. This absolutely helps me to understand the problem. But the solution has me a little stumped. I need to keep the date and time values in the history table. So how do I build a calendar table with a start date/time of 1/1/2020 12:00:00 am through a dynamic end date/time of datetime.LocalNow()?
Or do I add a column to the history table in power query that extracts the date only and use that as my relationship column? (I need to keep the time values for other measures.)
Which is more efficient or best practice?
I am hoping the answer is to create a column in the history table that extracts the date only because I am having a hella time trying to write the m-code for a calendar table that includes all the minutes between 1/1/2020 - now.
Hi Txtcher ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
Yes, the best and most efficient practice is to add a new column to your history table that contains only the date (without the time) and use that for your relationship to the calendar table.
This allows you to:
-
Maintain the original date/time values for analysis that needs time-level detail.
-
Use your calendar table (which only includes dates) effectively in slicers and visuals without needing a massive minute-by-minute calendar.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
-