Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

Seconds in SQL Query to be converted to HH:MM format in Power Bi and use Average in Card visual

Hi All,

Some where in Community, I found a DAX code and used it to build one for my requirement . For some reason, I am not able to acheive what I need.

Firstly, I have created a SQL Query which have IncidentID(Unique No), Date , Duration in Secs Columns .
Then I have used below formulae to create Average of duration (HH:MM format).
Then I have used this formulae in Card visualization . I am either getting First or last Average values as the Calculated column i have created is in text format . I tried changing it to Time format , But it has thrown me an error like - Cannot convert value '32:42:27' of type Text to type Date.

AvgTotDurTime_Houle =

VAR Duration = Query1[Time to Resolve sec]

VAR Hours = INT ( Duration / 3600)

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN

IFERROR(

IF (Query1[Time to Resolve sec]>0, CONCATENATE (H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )),BLANK())

,BLANK())

So, What I am looking is - Firstly , I need an average of Duration in secs in HH:MM format and then i want to use it in Card visualization as shown in pbix file image .

Note: I couldnt able to attach pbix file. hence posted an image for reference

Need Help

v-juanli-msft

7 years ago

Hi Anonymous

Change the queries as below

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to Resolve sec", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sum", each List.Sum(#"Changed Type"[Time to Resolve sec])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Count of Incident ID", each List.NonNullCount(List.Distinct(#"Added Custom"[Incident ID]))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Average", each [Sum]/[Count of Incident ID]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Hours", each Number.RoundDown([Average]/3600)),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Minutes", each Number.RoundDown(([Average]-3600*[Hours])/60)),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Seconds", each Number.RoundUp(Number.Mod([Average]-3600*[Hours],60))),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom5", {{"Hours", type text}, {"Minutes", type text}, {"Seconds", type text}}, "en-US"),{"Hours", "Minutes", "Seconds"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
in

If #"Added Custom5" doesn't use Number.RoundUp, it will show 5.38 for seconds, then after merging columns, it can't change type to duration, it would throw an error.

Best Regards

Maggie

2.pbix43 KB

8 Replies

Anonymous
Not applicable
7 years ago
Hi Team,

I have tried few more . Please see below.

Now the last measure "Hours" which is in decimal format should be converted to HH:MM format and the expected value is 05:50 (5 hours 50 mins ) . Please help me .
- Anonymous
  Not applicable
  7 years ago
  I have tried the below formulae , but it didnt worked .
  
  Hh:mm = FORMAT([Hours],"hh:mm")
v-juanli-msft
Community Support
7 years ago
Hi Anonymous

Open Edit queries,

Add custom columns

sum=List.Sum(#"Changed Type"[time to resolve sec]) count of id=List.NonNullCount(List.Distinct(#"Added Custom"[id])) average=[sum]/[count of id] hours=Int64.From([average]/3600) minutes=Int64.From(([average]-3600*[hours])/60) seconds=Number.Mod([average]-3600*[hours],60)

click on "setting" icon to open the window to see what steps i applied.

Merge columns:

select "hours", "minutes","seconds" at the same time, then select "merge columns"

Change "Merged" to Type "duration"

Close&&apply

In report view, change the data format to "time" for the "Merged" column

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Seconds in SQL Query to be converted to format in Power Bi and use Average in Card visual.pbix37 KB
- Anonymous
  Not applicable
  7 years ago
  Hi Maggie,
  
  Thanks for your reply. I followed all the steps given by you . But I am facing an issue with Minutes and Seconds Columns as they are appearing in negative values. Could you please check the screen shot i have attached and advise me some thing ?
  - Anonymous
    Not applicable
    7 years ago
    I have followed same steps which you have given . See the Columns i have created below.
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to Resolve sec", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sum", each List.Sum(#"Changed Type"[Time to Resolve sec])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Count of Incident ID", each List.NonNullCount(List.Distinct(#"Added Custom"[Incident ID]))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Average", each [Sum]/[Count of Incident ID]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Hours", each Int64.From([Average]/3600)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Minutes", each Int64.From(([Average]-3600*[Hours])/60)),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Seconds", each Number.Mod([Average]-3600*[Hours],60)),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom5", {{"Hours", type text}, {"Minutes", type text}, {"Seconds", type text}}, "en-US"),{"Hours", "Minutes", "Seconds"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
    in