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