Distinct list of DATES only from column of DATE+TIMES

[lpriceFTW]

##########################################################################################

# I am removing all html formatting because of a Fabric error preventing me from posting. Please bear with me on this #

##########################################################################################

How do I create a new table of unique DATES (1/1/2023) from a column of DATETIMES (1/1/2023 10:30am)?

Lets say I have a column of DATETIMES like so:

___________________
| 10/1/23 10:30am |
| 10/1/23 11:30am |
| 10/1/23 9:30am   |
| 10/2/23 1:30pm  |
| 10/3/23 8:30am  |
| 10/3/23 7:30pm  |
| 10/3/23 5:30am  |

I want a returned table of unique DATE values like so:

___________
| 10/1/23 |
| 10/2/23 |
| 10/3/23 |

DAX would be something like:

```` dates = values(bot_activity_date[started].[dates]) //DOES NOT WORK!!! ```

[smpa01 · Accepted solution]

@lpriceFTW  try this

EVALUATE
SUMMARIZE(ADDCOLUMNS(DATETIMES,"@yr", CONVERT(DATEVALUE(DATETIMES[colOne]),DATETIME)),[@yr])

[lpriceFTW]

This worked. Thank you!

[Anonymous]

Create a Calulated Column in table
FieldDate = FORMAT(DateTime, "mm/dd/yyyy")
Then, in Data view of pbix file, create a new Table - there is a button under table tools

Add a column UniqueDates = DISTINCT(FieldDate)

[lukiz84]

shorter without a need for a calculated column in the source table:

Table =
   DISTINCT(
       SELECTCOLUMNS(
          SUMMARIZE(SourceTable, SourceTable[DateCol], "DateFormatted", FORMAT(SourceTable[DateCol], "DD/MM/YYYY")), [DateFormatted])
)

[lukiz84]

do you want to use powerquery or dax (if dax, then calculated table or only for a measure?)? 

[lpriceFTW]

DAX - its for a calculated table.