How to change time format dynamically

[Anonymous]

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:

[Manoj_Nair]

@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

[Anonymous]

I don't know how to create a disconnected table. Please help me to create a table.