Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
webportal
Impactful Individual
Impactful Individual

How to create a calendar table with second granularity?

I've built a calendar table using Power Query for the whole year of 2022 using second granularity.

 

Basically, by "cross joining" a calendar table with day granularity (365 rows) with a time table (24*60*60 rows).

 

The table contains 31.536.000 rows, all distinct values for column DateTime.

 

Now, after relating this table with a transacional table, the visuals do not show any values.

 

Here's a sample of the transactional table:

 

webportal_0-1644001819903.png

Although all these timestamps are present in the calendar table, I get an empty matrix when trying to visualize the results.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

In the Transaction table does the column you're creating the relationship on have timestamps as well?  If not then you won't get any matches because the Dates aren't lining up to the Timestamps.

Generally speaking in data modeling we keep our Dates and Times separate.  First, it cuts down on data volumes (only 86400 rows in the Time table and 365/366 rows per year in the Date table). 
I would try that - separate your Date and Time dimension tables and separate your Dates and Times in the transaction table.  Still gives you the capability to drill-down to the second level without super-sizing your model.

View solution in original post

4 REPLIES 4
webportal
Impactful Individual
Impactful Individual

@Anonymous 

I've managed to relate the DateTime table with the transactional table by converting the timestamp into integers (as the difference in seconds to the 1st datetime). This worked perfectly.

 

I also separated the simple Date and Time into two different tables with the great advantage - as you well refer - to decrease the size of the model.

 

Thanks for helping!

webportal
Impactful Individual
Impactful Individual

@Anonymous 

Yes, both tables have timestamps, that's why I don't understand why the relationship doesn't work.

 

Thanks for the tip, but what if you want to have continuous visuals? Eg: a line chart for a certain week where you may scroll horizontally to the second?

Anonymous
Not applicable

My guess is that the matching on the timestamps simply isn't lining up (although it should - datetimes are stored as numbers under-the-sheets so I'm surprised it's not working).  I'd try the "resolve-it-to-yyyymmddhhmmss-format-and-join-on-that" approach.

As far as creating visualizations (if you have separate date and time dimensions) you could simply stack your date fields on the axis shelf and then stack your time fields underneath them and the drill-down/expand-down should work with no issues.

Anonymous
Not applicable

In the Transaction table does the column you're creating the relationship on have timestamps as well?  If not then you won't get any matches because the Dates aren't lining up to the Timestamps.

Generally speaking in data modeling we keep our Dates and Times separate.  First, it cuts down on data volumes (only 86400 rows in the Time table and 365/366 rows per year in the Date table). 
I would try that - separate your Date and Time dimension tables and separate your Dates and Times in the transaction table.  Still gives you the capability to drill-down to the second level without super-sizing your model.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.