Convert timestamp to date in DAX with DirectQuery

Question

I want to convert a Timestamp column to date column in my table. I tried to create a measure with DAX:

Date = FORMAT('my_table'[Timestamp], "dd/mm/yyyy")*1

and caught error:

A single value for column 'Timestamp' in table 'my_table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

but I do not need aggregation here I just need to convert datetime to date.

Timestamp column looks like this:

Tried:

Date = FORMAT(MIN('my_table'[Timestamp]), "dd/mm/yyyy")*1

which gives a weird date format, as I tried to inspect a visual using "show as a table":

You probably want to use a Calculated Column, not a Measure. — Mankarse
– Mankarse, Commented Apr 13, 2021 at 1:05
Thanks for the suggestion @Mankarse, would that work for DirectQuery? — nilsinelabore
– nilsinelabore, Commented Apr 14, 2021 at 1:43

Alexis Olson · Accepted Answer · 2021-04-13 15:26:29Z

2

Create a calculated column not a measure.

You can simply duplicate the timestamp column and then change the column data type to Date.

Date = 'my_table'[Timestamp]

answered Apr 13, 2021 at 15:26

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nilsinelabore Over a year ago

Hi thanks for the answer, I forgot to mention that I'm using DirectQuery data. It caught error This query contains transformations that can't be used for DirectQuery. when I tried to create a new column.

mkRabbani · Accepted Answer · 2021-04-13 03:58:21Z

2

Can you please try this below Measure-

Date = FORMAT(MIN('my_table'[Timestamp]), "dd/mm/yyyy")*1

answered Apr 13, 2021 at 3:58

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

3 Comments

nilsinelabore Over a year ago

Hi thank you for the answer. I tried and it seems like the date format is wrong, please see edited question.

mkRabbani Over a year ago

Hi @nilsinelabore, this is because the last part *1 I guess. Just remove that part and try again.

nilsinelabore Over a year ago

Thank you for your suggestion. I tried to remove *1 but I don't think it's what I wanted. I want to convert timestamp to date so I can count the events in each day but this method seems to extract only one day...

alexrai93 · Accepted Answer · 2024-08-18 12:41:25Z

1

When you use INT() function it will remove the time from the dates, so from "03/05/2024 04:13:00" Will make "03/05/2024 00:00:00"

So you just duplicate with INT

Date = INT('my_table'[Timestamp])

edited Aug 18, 2024 at 12:41

alexrai93

6785 silver badges10 bronze badges

answered Jul 31, 2024 at 10:14

Greg

111 bronze badge

Collectives™ on Stack Overflow

Convert timestamp to date in DAX with DirectQuery

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related