Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 !
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 !
Greg
Hi, @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.
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |