Search code examples
visual-studioformattingssasdaxssas-tabular

Formatting measures in DAX

I have a situation where I have a simple measure which gets the average of a column in a dimension. The column contains data in seconds. I need to format the measure in a way that show something along the lines of 00:00:00 ( Hour:Minute:Second). The existing measure is below. Appreciate any help.

**Measure**
Average:= Average(ColumnName)
Solution

  • Try this way, 

        Average: =CONCATENATE(IF(LEN(INT([ColumnName]/3600))=1,CONCATENATE("0",INT([ColumnName]/3600)),
CONCATENATE("",INT([ColumnName]/3600))),":",IF(LEN(INT(MOD([ColumnName],3600)/60))=1,
CONCATENATE("0",INT(MOD([ColumnName],3600)/60)),CONCATENATE("",INT(MOD([ColumnName],3600)/60))),":",
IF(LEN(MOD(MOD([ColumnName],3600),60))=1,CONCATENATE("0",MOD(MOD([ColumnName],3600),60)),
CONCATENATE("",MOD(MOD([ColumnName],3600),60))))

    Here I considered that [ColumnName] is already aggregated value.