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.
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.