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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rpinxt
Impactful Individual
Impactful Individual

Dax to make Time series from timestamp in direct query mode

Ok I have a report in direct query mode because I need auto refresh page.

Disadvantage here is that I cannot add a custom column because there is no data view for tables in direct query mode.

Means I cannot use M to make a time column based on my timestamp.

 

Saw that you can use DAX with the MROUND function, but I cannot adjust it so it itterates over all the lines.

In MROUND I can only chose my timestamp field if I use somehting like MAX:

Time = MROUND (MAX('AVN PPS Daily_LC1510'[lc_timestamp]), TIME ( 0, 30, 0 ) ) + TIME ( 0, 0, 0 )
 
But of course this only gives me the max timestamp rounded to the first half hour.
rpinxt_0-1683034918528.png

 

How would I make a Time field that converts on every line the timestamp to a time only field?

Want to uses this field as an x-axis in a column chart.

1 ACCEPTED SOLUTION

Hi @rpinxt 

 

A measure cannot be used as a dimension field, so it cannot be put on the x-axis of a column chart. You need a column on the x-axis. Luckily, you can still create a calculated column in this scenario. For example, 

Time = MROUND('DailyData'[lc_timestamp],TIME(0,30,0)) + TIME(0,0,0)

vjingzhang_1-1683184912056.png

 

In DirectQuery mode, you can create some simple calculated columns which only use intra-row data. Although you cannot see the data table, you can use visuals to display the calculated columns. They can also be used as dimension fields. Once you create the "Time" column, change its data type to Time to make it only contain time values. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

5 REPLIES 5
rpinxt
Impactful Individual
Impactful Individual

Ok I was able to do it with putting timestamp in a variable.

Then use ceiling and format to make it a time format.

TS_VAR =
VAR TS = SELECTEDVALUE('AVN PPS Daily_LC1510'[lc_timestamp])
RETURN
TS
 
Time = FORMAT(CEILING([TS_VAR],"0:30"),"h:mm AMPM")
 
rpinxt_0-1683038338742.png

 

 

Buttttt....this measure fiels I somehow cannot put on the x-axis of a column chart??

 

Anybody know why this is?

 

Hi @rpinxt 

 

A measure cannot be used as a dimension field, so it cannot be put on the x-axis of a column chart. You need a column on the x-axis. Luckily, you can still create a calculated column in this scenario. For example, 

Time = MROUND('DailyData'[lc_timestamp],TIME(0,30,0)) + TIME(0,0,0)

vjingzhang_1-1683184912056.png

 

In DirectQuery mode, you can create some simple calculated columns which only use intra-row data. Although you cannot see the data table, you can use visuals to display the calculated columns. They can also be used as dimension fields. Once you create the "Time" column, change its data type to Time to make it only contain time values. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

rpinxt
Impactful Individual
Impactful Individual

Superb! @v-jingzhang 

 

Thanks this indeed works just fine 😄

 

Old:

rpinxt_0-1683193498020.png

New:

rpinxt_1-1683193527854.png

 

Now the small differences are rounded.

Thanks!

 

 

rpinxt
Impactful Individual
Impactful Individual

Nope again too soon 😢

 

When I take out timestamp it still only show the latest timestamp....

 

Can it be done? A calculated field on a timestamp that returns the timestamp but as a time only field?

And when you take that field out the new field still shows all the times?

rpinxt
Impactful Individual
Impactful Individual

My god...sorry about this....

 

Of course the answer is MAXX 😂😖

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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