Forum Discussion
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.
Any help will be much appreciated. Thanks!
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
6 Replies
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 _LogicLoggged 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?
My Hour table
My MainTable:
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?
