Calculate column base on two rows in same timestamp

[Anonymous]

Hi i want to create a new column base on category F and G value, it need to be check for every timestamp, when F and G is over the Alarm it will be 1 otherwise 0. 

time	Categogy	Alarm	Temp
2021-12-14 07:01	A	20	8.08
2021-12-14 07:01	B	20	9.27
2021-12-14 07:01	C	20	6.67
2021-12-14 07:01	D	20	4.93
2021-12-14 07:01	E	20	4.07
2021-12-14 07:01	F	80	83.57
2021-12-14 07:01	G	100	103.4
2021-12-14 07:02	A	20	8.09
2021-12-14 07:02	B	20	9.28
2021-12-14 07:02	C	20	6.67
2021-12-14 07:02	D	20	4.94
2021-12-14 07:02	E	20	4.07
2021-12-14 07:02	F	80	83.74
2021-12-14 07:02	G	100	103.09
2021-12-14 07:03	A	20	8.11
2021-12-14 07:03	B	20	9.3
2021-12-14 07:03	C	20	6.68
2021-12-14 07:03	D	20	4.96
2021-12-14 07:03	E	20	4.07
2021-12-14 07:03	F	80	84.07
2021-12-14 07:03	G	100	102.58

[amitchandak]

@Anonymous , what do mean by "when F and G is over the Alarm"

Can you share expected output

[Anonymous]

Yes like this:

time	Category	Alarm	Temp	output
2021-12-14 07:01	A	20	8.08	0
2021-12-14 07:01	B	20	9.27	0
2021-12-14 07:01	C	20	6.67	0
2021-12-14 07:01	D	20	4.93	0
2021-12-14 07:01	E	20	4.07	0
2021-12-14 07:01	F	80	83.57	1
2021-12-14 07:01	G	100	103.4	1
2021-12-14 07:02	A	20	8.09	0
2021-12-14 07:02	B	20	9.28	0
2021-12-14 07:02	C	20	6.67	0
2021-12-14 07:02	D	20	4.95	0
2021-12-14 07:02	E	20	4.07	0
2021-12-14 07:02	F	80	83.74	1
2021-12-14 07:02	G	100	103.09	1
2021-12-14 07:03	A	20	8.11	0
2021-12-14 07:03	B	20	9.3	0
2021-12-14 07:03	C	20	6.68	0
2021-12-14 07:03	D	20	4.96	0
2021-12-14 07:03	E	20	4.07	0
2021-12-14 07:03	F	80	74.07	0
2021-12-14 07:03	G	100	102.58	0

if it in same timestamp and categoy G is over the alarm and category f is over the alarm then 1 
if categoy G is over the alarm but not category f then 0 , both G and F need to be over the Alarm value to set it 1. 

[ryan_mayu · Accepted solution]

@Anonymous 

you can try this

Column = 
VAR _nextalarm=maxx(FILTER('Table','Table'[Category]="G"&&'Table'[time]=EARLIER('Table'[time])),'Table'[Alarm])
VAR _nexttemp=maxx(FILTER('Table','Table'[Category]="G"&&'Table'[time]=EARLIER('Table'[time])),'Table'[Temp])
VAR _lastalarm=maxx(FILTER('Table','Table'[Category]="F"&&'Table'[time]=EARLIER('Table'[time])),'Table'[Alarm])
VAR _lasttemp=maxx(FILTER('Table','Table'[Category]="F"&&'Table'[time]=EARLIER('Table'[time])),'Table'[temp])
RETURN IF('Table'[Category]="F"&&'Table'[Temp]>'Table'[Alarm]&&_nexttemp>_nextalarm,1,if('Table'[Category]="G"&&'Table'[Temp]>'Table'[Alarm]&&_lasttemp>_lastalarm,1,0))