Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
##########################################################################################
# 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
92 | |
81 | |
70 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |