Forum Discussion
Intersection between two duration
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
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 !
GregHi, GregB49
I'm sorry, because your needs are more complicated and the calculations are more time-consuming, I also had some troublem,so didn't reply in time.
You can refer my sample pbix file. I calculate it in PQ, since I can't access the source data in your file, I didn't consider the state when I did it in my sample. The result is a bit big. As long as you filter out the state = on in the PQ, you can execute it.
Secondly, the calculation interval takes a long time, because every time between each device must be compared, first determine whether there is an intersection, then calculate how much the intersection, and finally calculate the sum of each day. The amount of calculation is very large, so many days are not recommended.
There are a lot of steps, you need to write many querys in pq, you can check it first, if you don’t understand, ask me, I will describe the steps later.
Here is my sample file. Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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