dealing with time and ranges in dax

[cuseman03]

Hi...man I've wasted so many hours today trying to figure this out.  I have two needs.  first, I have a column that already calculates the duration between two times.  The format of the results is 'hh:mm:ss'.  I have values ranging from '00:00:02' to '30:38:02'.  I want to plot these so first thing is how do i do a count where I can show total number for seconds, hours, and mins.  Then, I want to get deeper and get a count for say a range from 5 mins to 45 mins.  Or for 0-1 hours and then say >2 hours.  I've tried the conversion daxconvertsecondstotime · GitHub to no avail.  The good news is that I have start times and end times split into dates and times columns, so it's easy to get the duration.    What I can't do is associate counts to custom time ranges I want to create.

Thanks.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin ,for your response.

Hi @cuseman03,

We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.

Please find attached the screenshot and PBIX file, which may help resolve the issue:

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[cuseman03]

perhaps you can help with 2 additional matters.  I created a series for all 24 hours, in one hour increments.  I created an adjoining table to sort them earliest to latest and when I drop the sort filed in the tool tip it just doesn't work.  I essentially want to be able to sort the X axis by a field in another table that does not show up in the chart

as you can see from my screen shots, I have a sort table, it's a one-many join, and I apply that sort in the line chart but it doesn't come out right

the last thing is how to handle this.  If I have an ETL that starts at 9:10pm and ends at 12:10am, I need to have a count of 4, one for hour 9-10, one for hour 10-11, one for hour 11-12, and one for 12am-1am.  How can i do this?

[cuseman03]

you are amazing.  thank you!  i'd love to actually learn how you did this.

[cuseman03]

I've uploaded a screen shot.  So I have a list of ETLs.  Each ETL has steps that run daily, sometimes more than one time daily.  I have a full date/time start and end column.  I also took the duration and converted it to seconds to get total seconds.  First thing I want to do is create a group, say 9am-12pm and get a count of ETLs that start and end between these times.  In PBI in the report view, my 'stepstart' and 'stepend'columns only show date, no time.  

The next thing I want to do is standardize on duration.  My logic is basically, 'IF 'total seconds' <=60, do nothing'.  This obviously means it's 60 seconds or less.  Then I want IF 'total seconds' >=3600 (we know we're dealing in hours since 3600 secs=1 hour) divide by 3600 to get hours.  For mins, >60 and less than 3600.  This way I could say X ETLs take X mins, Y secs, and Z hours.  

How would I create a group that says something like 0-5 mins, or >10 mins?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

[cuseman03]

not sure how i can do that since my pbix is linked to back end SQL Server and I can't share that

[lbendlin]

instead of posting a screenshot , post the data as a text table.

I asked CoPilot but it balked after a small portion. Anyway, here is one example of how you can visualize this

Total duration is SUMX(Table,[StepEnd]-[StepStart]) - that's all you need. You can use custom formatting if total duration exceeds 24 hrs.

[lbendlin]

The good news is that I have start times and end times split into dates and times columns

How is that good news?  You need to add these columns back together for this to work.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...