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