Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joyhackett
Helper II
Helper II

Find jobs that were running during specific times

Hi!

 

We have a report that tracks our warehouse ETL jobs.  This is an example table of the jobs:

 

Job#StartEnd
112/15/22 7:00am12/15/22 8:00am
212/15/22 7:23am12/15/22 7:50am
312/15/22 7:45am12/15/22 8:33am
412/15/22 9:44am12/15/22 10:30am


I am trying to solve for the following:

1. I would like to have a chart that shows how many jobs were running at every minute

Date/Time# jobs running
12/15/22 7:22 am1
12/15/22 7:23 am2
... 
12/15/22 7:50 am3
12/15/22 7:51 am2


2. I would also like to be able to select MULTIPLE date/times (from a slicer) and see which jobs were running (to look for similarities)
User selects: 12/15/22 7:30am & 12/15/22 10:02am
Results: 

Jobs
1
2
4


Ideally, I would love to have a graph that shows all the jobs running for each minute with the ability to multiselect time ranges (and then return which jobs are in that selection), but I can't find anything on the appstore that exists like this:

joyhackett_0-1671234543561.png

 

Thank you so much for your help in advance! 

🙂 Joy

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@joyhackett , You need a date table with date and minute, you can generate that using List.DateTimes

 

Power Query- List.DateTimes- https://youtu.be/3UBxsIvlcRQ

Give duration as #duration(0,0,1,0)

 

You can keep that as an independent table and have measures like

 

CALCULATE(COUNTx(FILTER(Table,Table[Start]<=max('DateTime'[DateTime]) && Table[End]>max('DateTime'[DateTime])),Table[Job #]))

 

you can refer for formula

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

View solution in original post

2 REPLIES 2
joyhackett
Helper II
Helper II

Thank you so much! 

I went a slightly different route and generated a List.DateTime in PowerQuery  and created a new table with all the possible datetimes. Your method is MUCH cleaner!

amitchandak
Super User
Super User

@joyhackett , You need a date table with date and minute, you can generate that using List.DateTimes

 

Power Query- List.DateTimes- https://youtu.be/3UBxsIvlcRQ

Give duration as #duration(0,0,1,0)

 

You can keep that as an independent table and have measures like

 

CALCULATE(COUNTx(FILTER(Table,Table[Start]<=max('DateTime'[DateTime]) && Table[End]>max('DateTime'[DateTime])),Table[Job #]))

 

you can refer for formula

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors