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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
KalvisTe
New Member

Filter visual based on if process is in time range

Hi, 

I need help how could I filter how many processes are running at a given time.

I have a table where there are 4 columns, Process name, Start Time, End Time and Country. I want to calculate how many processes are running at the same time for a a given time period. Most likely I'll filter by 1 hour periods, like 8:00-9:00 and 12:00-13:00. 

I can't figure out how to write a calculated table/column or maybe a DAX measure that could show this.

Since I'm a newbie and don't usually work with PowerBI i need some help and guidance, what could be the best approach to this? 

 

Do I need to create additional table with all given hours of the day? Table looks something like this, but it has more than 100 rows. 

So the main goal is to have a visual with maybe 24hour period with time stamps that shows how many processes are running at the same time at that given time period. 

KalvisTe_0-1695491599111.png

Any help will be much appreciated. Thanks! 

 

1 ACCEPTED SOLUTION
gmsamborn
Super User
Super User

Hi @KalvisTe 

You are on the right path with an Hour table.

After adding an hour, you can use a measure like this...

Active = 
VAR _Hr = SELECTEDVALUE( 'Hours'[Hour] )
VAR _Logic =
    CALCULATE(
        COUNTROWS( 'Processes' ),
        FILTER(
            'Processes',
            'Processes'[Start Time] <= _Hr
                && 'Processes'[End Time] >= _Hr
        )
    )
RETURN
    _Logic

 

Active Processes.pbix

 

 

View solution in original post

6 REPLIES 6
gmsamborn
Super User
Super User

Hi @KalvisTe 

You are on the right path with an Hour table.

After adding an hour, you can use a measure like this...

Active = 
VAR _Hr = SELECTEDVALUE( 'Hours'[Hour] )
VAR _Logic =
    CALCULATE(
        COUNTROWS( 'Processes' ),
        FILTER(
            'Processes',
            'Processes'[Start Time] <= _Hr
                && 'Processes'[End Time] >= _Hr
        )
    )
RETURN
    _Logic

 

Active Processes.pbix

 

 

Loggged in with work account. I can't understand why I get nothing on my visuals. I think that I have done everything correctly, double checked with the file you provided. Any suggestions as what could be wrong?

KalvisTete_3-1695708877994.png

 

My Hour table

KalvisTete_0-1695708697960.png

My MainTable:

KalvisTete_1-1695708818736.pngKalvisTete_2-1695708830786.png

 

Hi @KalvisTete  @KalvisTe 

I'm not sure what you problem might be.  Your setup looks correct.

Can you show me how you are trying to use it?  ie. a table visual with Hour and [Active] measure like in my example OR are you trying to use it in a different scenario?

 

I tried it multiple ways. With a table, with a visual. Same result, nothing shown. It has got to with how the data was imported. I created a small table manually, with the columns and values. It works then. I'll work around this somehow. 

KalvisTete_0-1695790675243.png

@KalvisTete 

Sorry.  I wish I could help but there's not much I can do at this point.

Anyway, thanks for your help! Your measure worked great! I just figured out the reason for it not showing the values. So in my Data pane i changed the values to Time, but in query it was still shown as Date/Time, changed it there as well and it works now! 🙂 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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