Dax to make Time series from timestamp in direct query mode

[rpinxt]

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.

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.

[rpinxt]

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")

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

Anybody know why this is?

[v-jingzhang · 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)

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]

Superb! @v-jingzhang 

Thanks this indeed works just fine 😄

Old:

New:

Now the small differences are rounded.

Thanks!

[rpinxt]

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]

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

Of course the answer is MAXX 😂😖