Forum Discussion
Row and column wise conditional difference
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.
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.
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.
Thank you amitchandak . Please refer my comment below as well.
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.