Forum Discussion
How to change time format dynamically
How to change date time format dynamically by using the single column. When i am going to select on 24 hours in filter page,date time should be reflect as (mm/dd//yyyy,[hh:tt] ) and when i am going to select on days(like 10d,7d,30d,etc), date format should come as (mm/dd/yyyy). Both formats should be get in same filter.
How this can be achive by using DAX query in single column in powerBI. Please find the sample screenshot.
For 24 hours:
For 5 days:
2 Replies
- Manoj_Nair
Solution Supplier
Anonymous- Check this out and let me know if this work.
Create two separate calculated columns - one for the 24 hours format, and the other for the days format:
dax
DateTimeFormat = FORMAT('Table'[DateTime], "MM/DD/YYYY, hh:mm") DateFormat = FORMAT('Table'[DateTime], "MM/DD/YYYY")Create a disconnected table with the two format options ("24 hours", "Days").
Create a measure that returns the appropriate format based on the selection in the disconnected table:
dax
SelectedFormat = SWITCH( SELECTEDVALUE('DisconnectedTable'[FormatOption]), "24 hours", MAX('Table'[DateTimeFormat]), "Days", MAX('Table'[DateFormat]), BLANK() )In this workaround, you would use the SelectedFormat measure in your report, and the user would select the format by using a slicer based on the disconnected table. The measure would return the date/time in the selected format.
This is just a workaround, not sure if we can do this dynamically by creating a measure.
Let me know how this shapes up in your model. Thanks
