Row and column wise conditional difference

[Anonymous]

I have created a power BI dashboard to monitor the emails coming to a shared mailbox (MS office 365 account/exchange). I need help to write a dax query to create a new column (Duration in hours). As shown below for every OFF event of same name and serial I need to check the subsequent rows for an ON event (same name and serial) and then find the difference between Date/time stamp. This will result in getting the duration between ON/OFF even of each name / serial.

Example: The OFF event of Alarm 1 Hi (serial 1234) @ 3/10/2020 0:00 has a equivalent ON event in the subsequent rows @ 3/8/2020 12:00 (Alarm 1 Hi , serial 1234). Subtracting the date/time stamp of these two give me 36 hours.

Email	Serial	Name	ONOFF	Date	Duration hrs
Email 12	1234	Alarm 1 Hi	OFF	3/12/2020 0:00	24.00
Email 11	1234	Alarm 1 Hi	ON	3/11/2020 0:00
Email 10	1234	Alarm 1 Hi	OFF	3/10/2020 0:00	36.00
Email 9	1234	Alarm 2 Hi	OFF	3/9/2020 0:00	46.50
Email 8	1234	Alarm 1 Hi	ON	3/8/2020 12:00
Email 7	1234	Alarm 2 Hi	ON	3/7/2020 1:30
Email 6	1234	Alarm 1 Lo	OFF	3/6/2020 0:00	24.00
Email 5	1234	Alarm 1 Lo	ON	3/5/2020 0:00
Email 4	5678	Alarm 1 Hi	OFF	3/4/2020 0:00	24.00
Email 3	5678	Alarm 1 Hi	ON	3/3/2020 0:00
Email 2	5678	Alarm 1 Hi	OFF	3/2/2020 19:09	24.03
Email 1	5678	Alarm 1 Hi	ON	3/1/2020 19:07

[amitchandak · Accepted solution]

@Anonymous , is there any change in the solution you want in the initial post. I posted a solution where email 10 was not matching as email 9 off and email 10 is off too. Have you reviewed that?

[amitchandak]

Please find the solution at : https://www.dropbox.com/s/s09wp65z2ma2b0j/diffwithlastOn.pbix?dl=0

Did not get logic for 36 at one place

Appreciate your Kudos.

[ImkeF]

Hi @amitchandak  

the match has to be on "Name"-level as well.

Then you'd have a pair with email 10 and email 8.

[Anonymous]

Thank you @amitchandak . Please refer my comment below as well.

[amitchandak · Accepted solution]

@Anonymous , is there any change in the solution you want in the initial post. I posted a solution where email 10 was not matching as email 9 off and email 10 is off too. Have you reviewed that?

[ImkeF · Accepted solution]

Hi @Anonymous 

please check attached solution.

It contains a DAX and a Power Query solution.

If you're importing hundreds/thousands of emails, PQ might not play out it's advantages here. 

Would be interested to hear which version runs faster for your case.

[ImkeF]

Attaching the file with the adjusted DAX - solution from @amitchandak ,

if you run into performance issues, please come back with my questions answered .

core of that solution is a new column that contains the corresponding "ON"-Value in each "OFF"-row:

Last Close =
IF (
    Sheet1[ONOFF] = "OFF",
    MAXX (
        FILTER (
            Sheet1,
            ( Sheet1[Date] ) < EARLIER ( Sheet1[Date] )
                && Sheet1[ONOFF] = "ON"
                && Sheet1[Serial] = EARLIER ( Sheet1[Serial] )
                && Sheet1[Name] = EARLIER ( Sheet1[Name] )
        ),
        Sheet1[Date]
    ),
    BLANK ()
)

But with 3 EARLIERs it will probably not run fast on large datasets.

[Anonymous]

@ImkeF Thank you very much. I was not sure which solution to opt so asked for DAX assuming it would be faster. This linked mailbox will receive a lot of emails. Also as you had asked for each ON will there be a equivalent OFF. Yes, it it will be but it might take some days as well. Say I recieve the ON today, The OFF email might be recived even after 3 days. In those cases i would like to use todays date/time stamp (at that moment) until I get the OFF event and display the duration. Sorry I missed to mention this earlier. The intention here I would like to monitor those ON events which are active without OFF for more than 24 hours. Please help.

[ImkeF]

Hi @Anonymous  

may I ask why you want a DAX-solution for it?

A solution in the query editor would most likely be faster and compress better.

Also: Is it guaranteed that every OFF-event has a corresponding ON-event like in your sample data (and vice versa) or could there be single ON or OFFs without a counterpart?