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, 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
Hi, 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 !
GregHi, GregB49
I try to achieve your needs based on the original data, so I can't change it as you did, but the original data is really not easy to do. Even if it takes a lot of time to do it like me, the query calculation will take a lot of time. I'm glad you can find the right method by yourself. You can accept your answer as solution.
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.