Counting Active Processes

[TheLoonies]

I want to create a calculated table or measure or visual which takes in a date and returns the count of the amount of processes which were in progress on that date.

For example:

Process	start time	end time		time period	Count active
A	1	5		1	2
B	2	4		2	4
C	6	9		3	3
D	1	2		4	4
E	4	7		5	5
F	2	9		6	4
G	5	5		7	4
H	5	7		8	2
				9	2

I want to turn the data on the left into the data on the right.

This would be easy enought to do in power query, however I need the table on the right to change when a user filters the table on the left. 

Any Ideas on how to get this done? It might be possible to create a custom visual to do it, but I'm checking whether there is a better way before I dive into that.

[TheLoonies · Accepted solution]

I managed to get this to work how I wanted it to.

the table on the left is named ExampleData.

First I created a table of times from the data:

TimeTable = GENERATESERIES(MIN('ExampleData'[start time]),MAX('ExampleData'[end time]))

Then I used the following measure :

ActiveProcesses =

var MaxTime = MAX('TimeTable'[Value])

var MinTime = MIN('TimeTable'[Value])

RETURN

CALCULATE(COUNT('ExampleData'[Process])+0,

MaxTime >= 'ExampleData'[start time],

MinTime <= 'ExampleData'[end time])

Then I am able to plot 'Timetable'[value] against ActiveProcesses for the desired effect.

[Ahmedx]

or need creat new tablr like this

Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiSRyYZQRTai6JWa2?e=SKEWjz

Table 2 = SUMMARIZE(GENERATE('Table', GENERATESERIES([start time],[end time],1)),[Value], "count", CALCULATE(COUNT('Table'[Process])))

 

[TheLoonies]

The problem is that the table doesn't change when I apply filters:

here with only process F selected I would want 0 for value = 1 and 1 for value > 1

[Ahmedx]

please again try

https://1drv.ms/u/s!AiUZ0Ws7G26RiSiFvKxhw760WwlA?e=dQrseY

[TheLoonies]

It works, but it's not exactly what I was hoping for; the approach lacks flexibility.

It feels like a workaround to make what I'm trying to do work with equalities, but the workaround requires multiplying the sizes of tables. I would be going from 10,000s of rows to millions.

[Ahmedx]

pls see my video

https://1drv.ms/v/s!AiUZ0Ws7G26RiSNORKRMHvAwGVLO?e=ooRecY

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.