Forum Discussion
Intersection between two duration
Hi everyone,
I'm here for a new problem ! First a little sum-up.
I have 2 sensors linked to 2 devices. Each one send information about the state of the device. example :
As you can see, I have :
A start date, an End Date, the device concerned, the state (on or off) and the duration of the state.
Now, what would be interesting for me, would be to have the "intersection" (not sure of this word used, I'm french...) of the sensor when they are both ON. To be honest, I don't have any idea of how to do it...
Since it's always easier to get understand with a picture :
The schema is just to explain the problem, I don't need to visual it, just to make measure with it.
Many thanks !
Grégoire
Hi Janey,
Sorry for my late reply. I tried to use you pbix file, and to be honest it didn't really work as it shoud. 🙂
I'm sure it's my fault, don't worry. Nethertheless, I succeded by doing something else. Here is the idea if someone needs it :
- I "un-pivoted" my columns so I have the devices on different columns.- I used (in PQ) the fill up option, from the top to the bottom. So the if there is a blank (like if a device give its state and not the other), it will take the last state declared.
- Now I have for each time, 2 declarations for the 2 devices. So it is way easier. Just have to filter my table with State device 1 ="On"&&State device 2="On".
It may not be the best solution, but for now, it is the best I have. 🙂
Anyway, many thanks to you Janey, for having helping me !
Greg
7 Replies
Hi, GregB49
If I understand correctly, you want to calculate the duration of the two devices being turned on, right? What I want to ask is, is the final result the sum of the duration? Can you provide some sample data in English and your desired result? So we can help you soon.
Best Regards
Janey Guo
Hi v-janeyg-msft,
It took me some time to strip my file, but here it is :
https://www.dropbox.com/s/zqmddn06wypwqrp/Time_cross.pbix?dl=0
I translated eveything in english.
The desired result would be to calculate the sum of the "common" duration between the 2 devices.
So I could choose a date and see how long the 2 devices were working together (both ON).
Example :
Date Shared time
02/15/21 16h35
02/16/21 11h21
Ask me again if I'm not clear enough 🙂Hi, GregB49
Okay, your idea is definitely achievable, but I still have a question. If you answer me, I can help you soon. For the duration of each date, is it within one day, or from the earliest time to this day?
Best Regards
Janey Guo
Hi v-janeyg-msft ,
I hope I understand well your question, so I guess it is within one day.
Thank you for your help !
Greg
