cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bebo
Helper I
Helper I

Measure to Calculate Number of Concurrent Events

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.

1 ACCEPTED SOLUTION

Hi @Bebo ,

 

Try this

 

 

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

 

 

vstephenmsft_0-1637315078682.png

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

vstephenmsft_1-1637315175898.png

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.

 

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

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.

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 TimeCitySensor NumberEvent TypeOn (1) / Off (0)
2021-11-02T00:00:40+0000  Miami BCDDC2F1F615 radar0
2021-11-02T00:00:40+0000  Miami BCDDC2F1F615 radar1
2021-11-02T00:00:40+0000  Miami BCDDC2F1F669 radar0
2021-11-02T00:00:40+0000  Miami BCDDC2F1F669 radar1
2021-11-02T00:00:40+0000  Miami BCDDC2F1F01D radar0
2021-11-02T00:00:40+0000  Miami BCDDC2F1F01D radar1
2021-11-02T00:00:40+0000  Miami BCDDC2F1ED1D radar1
2021-11-02T00:00:40+0000  Miami BCDDC2F1ED1D radar0

 

Hi @Bebo ,

 

Try this

 

 

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

 

 

vstephenmsft_0-1637315078682.png

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

vstephenmsft_1-1637315175898.png

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.

 

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

viviank
Resolver I
Resolver I

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.

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.

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors