Forum Discussion
Row and column wise conditional difference
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.
| 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 |
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?
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.
8 Replies
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?
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.
Hi amitchandak
the match has to be on "Name"-level as well.
Then you'd have a pair with email 10 and email 8.
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.