Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
##########################################################################################
# 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:
Solved! Go to Solution.
@lpriceFTW try this
EVALUATE
SUMMARIZE(ADDCOLUMNS(DATETIMES,"@yr", CONVERT(DATEVALUE(DATETIMES[colOne]),DATETIME)),[@yr])
@lpriceFTW try this
EVALUATE
SUMMARIZE(ADDCOLUMNS(DATETIMES,"@yr", CONVERT(DATEVALUE(DATETIMES[colOne]),DATETIME)),[@yr])
This worked. Thank you!
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)
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])
)
do you want to use powerquery or dax (if dax, then calculated table or only for a measure?)?
DAX - its for a calculated table.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |