Measure to Calculate Number of Concurrent Events

[Bebo]

I have a dataset that is refreshed daily that collects event / trigger data from a group of sensors that are individually triggered either by touch or radar for a given location (city). I can provide a sample of the dataset.

The question I am trying to answer is how many sensors are triggered (value of 1) at the same time and then construct visuals of the concurrent values over time. I am new to Power Bi and am trying to develop measure(s) to do this.

Thanks for any help you can give me.

[v-stephen-msft]

Hi @Bebo ,

Please provide sample data and expected results, thank you. Pay attention to hiding private data.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bebo]

An excerpt from the dataset is below. Please note that 4 different sensors are being triggered at the same time (same second). I am trying to develop a measure to calculate this and then create visuals with x axis being date/time and y being this new measure with objective to show concurrent sensor triggers over time.

Event Time	City	Sensor Number	Event Type	On (1) / Off (0)
2021-11-02T00:00:40+0000	Miami	BCDDC2F1F615	radar	0
2021-11-02T00:00:40+0000	Miami	BCDDC2F1F615	radar	1
2021-11-02T00:00:40+0000	Miami	BCDDC2F1F669	radar	0
2021-11-02T00:00:40+0000	Miami	BCDDC2F1F669	radar	1
2021-11-02T00:00:40+0000	Miami	BCDDC2F1F01D	radar	0
2021-11-02T00:00:40+0000	Miami	BCDDC2F1F01D	radar	1
2021-11-02T00:00:40+0000	Miami	BCDDC2F1ED1D	radar	1
2021-11-02T00:00:40+0000	Miami	BCDDC2F1ED1D	radar	0

[v-stephen-msft · Accepted solution]

Hi @Bebo ,

Try this

Count = COUNTROWS(FILTER(ALLEXCEPT('Table','Table'[Event Time],'Table'[City]),[On (1) / Off (0)]=1))

ALLEXCEPT is to group by time and city. [On (1) / Off (0)]=1 means to filter the triggered rows.

Since my data is very small, no lines are generated.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bebo]

Than you SOOO much - this is very helpful. I really appreciate it.

[viviank]

Clarify what "at the same time" means - within 1 minute, 1 second, 1 hour?

Then you can add another field with the time truncated appropriately, for example, if you want everything in the same hour, you can truncate the time value to the hour - 4:45:03pm becomes 4:00pm.

Then you can put this new field onto visualizations, and see at a glance when there are multiple events in the same time period - or you could create a measure to count them if you want to work with the actual count.

[Bebo]

Sorry for the very late response.

At the ame time is 1 second time frame. What I am triying to do is calculate how many senors (Sensor Number) are triggred each second.