topic Re: how to create a calendar table with time only - hh:mm:ss granularity? in DAX Commands and Tips

how to create a calendar table with time only - hh:mm:ss granularity?

agwalsh1812 — Thu, 13 Jun 2024 07:36:55 GMT

I want to create the following - preferably with DAX - I want to create a table that starts at 00:00:00 and finishes at 00:23:59 so I can map it to a column of times. Can I use the Calendar() function using Time to do that?

Also I have a number of columns with Time - would I be better off creating one Time table and then using Dax with UseRelationship() in the measure to activate them or separate Time Tables for each of the columns? Thank you as always.

Re: how to create a calendar table with time only - hh:mm:ss granularity?

ravsha85 — Thu, 13 Jun 2024 08:10:32 GMT

Hi agwalsh1812 you can try with this , convert the column to time once applied also you can make use of USERELATIONSHIP for connecting multiple time columns

Time=

SELECTCOLUMNS (

GENERATESERIES (

TIME(0,0,0),

TIME(23,59,59),

TIME(0,1,0)

"Time", FORMAT([Value], "HH:mm:ss")

)

Re: how to create a calendar table with time only - hh:mm:ss granularity?

agwalsh1812 — Thu, 13 Jun 2024 10:15:55 GMT

hi, that worked beautifully. I create a new table and then used the code. I made one tweak though

Time =

SELECTCOLUMNS (

GENERATESERIES (

TIME(0,0,0),

TIME(23,59,59),

TIME(0,0,1)

"Time", FORMAT([Value], "HH:mm:ss")

because I wanted the time to increment in seconds . thank you so much.

Re: how to create a calendar table with time only - hh:mm:ss granularity?

ravsha85 — Thu, 13 Jun 2024 12:06:14 GMT

Glad it worked for you🤝