Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GregB49
Helper I
Helper I

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 : 

GregB49_0-1615535182841.png

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 : 

GregB49_1-1615535590491.png

 

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



1 ACCEPTED 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

View solution in original post

7 REPLIES 7
GregB49
Helper I
Helper I

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.

v-janeyg-msft
Community Support
Community Support

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.