DAX Help

[Anonymous]

https://drive.google.com/file/d/1NGZKDUHs_HgNBsxVUBnoArEyc0rjK-aX/view?usp=sharing Hi,

Need to convert below data to duration and find the Average Time.

Notes:

1. The source is excel

2. the default datatype is Text when export from excel.

3. The Duration could be more than 99 Hrs ( While changinf the datatype if the Datapoint is more than 24 Hrs shows error)

3. attached Sample data  and view

https://drive.google.com/file/d/1NGZKDUHs_HgNBsxVUBnoArEyc0rjK-aX/view?usp=sharing  

[dax]

Hi chandra_0202, 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

In the attachment Sconds doesnt works, Let me clear my requirement / query in steps.

while import data from excel, the data points in Column Durations(HH:MM:SS) in excel gets  imporated as Text Datatype in Power BI.

Need to convert them in HH:MM:SS.

The time above 99 Hrs is getting blank after using Time Datatype and changed the format in Power Query.

Hence, my primary requirement is in power Bi need to change the format in HH:MM:SS and the datapoint above 99 Hrs shoud be converted.

Ex: 118:25:55

Here 118 Hrs is spend which i could not able to convert the same.

Please check the view below.

PLease let me know if you need further clarification

Regards.

[dax]

Hi chabdar_0202,

The time type in powerbi is 24h format instead of duartion. So I think you need to convert it to second , then calculate and convert it to hh:mm:ss in measure. You could refer to my measure in sample. If you want to convert it in M code, you could try below M code

let
    Source = Excel.Workbook(File.Contents("C:\Users\zoezhi\Downloads\sampl.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Response Time (H:M:S)", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Response Time (H:M:S)", "Response Time (H:M:S) - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Response Time (H:M:S)", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Response Time (H:M:S).1", "Response Time (H:M:S).2", "Response Time (H:M:S).3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Response Time (H:M:S).1", Int64.Type}, {"Response Time (H:M:S).2", Int64.Type}, {"Response Time (H:M:S).3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Response Time (H:M:S).1", "H"}, {"Response Time (H:M:S).2", "M"}, {"Response Time (H:M:S).3", "S"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each [H]*3600+[M]*60+[S]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Average(#"Added Custom"[Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"H", "M", "S"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type3", "Custom.2", each Number.ToText(Number.RoundDown ( [Custom]/ 3600, 0 ))&":"& Number.ToText(Number.RoundDown ( Number.Mod( [Custom], 3600 ) / 60, 0 ))&":"&Number.ToText(Number.RoundDown(Number.Mod( [Custom], 60 ) ))&":"&Number.ToText(Number.Round(Number.Mod([Custom], 1 ) * 100, 0 ))),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "convert hh:mm:ss"}})
in
    #"Renamed Columns1"

But this will show text type. So I suggest you convert its format in measure whiich you could calculate aggregation.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JarroVGIT]

@dax  and @Anonymous 

Could you please explain why my solution is not meeting your requirements? As far as I understand it, it does exactly what you want and showing 118:MM:SS as a result..

[JarroVGIT · Accepted solution]

Don't know what you've tried yet, but see this PBIX for a solution. https://1drv.ms/u/s!Ancq8HFZYL_aiIdb2MZyBmmB9sjtqA?e=anIhrM 

I do some PowerQuery stuff (split column, calculate a DurationSeconds column) and then i've created the measure AverageDuration which returns it into your original format (e.g. 118:46:06)

Let me know if this works for you! Kudo's are appreciated.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[ToddChitt]

What you have IS duration in H:M:S. But to calculate AVERAGE, that should be done as a Measure in DAX, and for that you need a NUMERIC or INTEGER column in your dataset.

Use Power Query to split the text column based on every instance of a delimiter : then create a custom column that does the math:

[Hours] * 3600 + [Minutes] * 60 + [Seconds]. 

Hope this helps