If and then statement between rows (finding a sequence)

[sabin_arsenal]

Hello, 

I am new to Power bi and wondering if you could help with some problem solving. I have a data set and below are a part of it, I am looking for a sequence - YYNN and  Marking the second Y in the sequence as a Y or else N in a new column

So the logic in the new colum would be 

when new record is available(e.g index 35)

the values in the new column at each row would be applying the following logic

If SW_H_B in index - 1 = Y

and SW_H_B in Index 0 = Y

and SW_H_B in Index +1 = N

and SW_H_B in index + 2 = N

then Y or else N

Column computed value is expected result marked in red. 

Many thanks for your help.

Index	SW_H_B	Computed value
0	N	N
1	N	N
2	N	N
3	N	N
4	N	N
5	Y	N
6	Y	N
7	N	N
8	Y	N
9	N	N
10	Y	N
11	N	N
12	N	N
13	N	N
14	Y	N
15	N	N
16	N	N
17	N	N
18	Y	N
19	Y	N
20	Y	N
21	Y	Y
22	N	N
23	N	N
24	Y	N
25	N	N
26	N	N
27	N	N
28	N	N
29	N	N
30	Y	N
31	N	N
32	Y	N
33	Y	Y
34	N	N
35	N	N

[camargos88]

Hi @sabin_arsenal ,

Try this m code:

try
if #"Changed Type1"{[Index]-1}[SW_H_B] = "Y" and
[SW_H_B] = "Y" and
#"Changed Type1"{[Index]+1}[SW_H_B] = "N"
and #"Changed Type1"{[Index]+2}[SW_H_B] = "N"
then "Y" else "N"
otherwise "N"

Replace the #"Changed Type1" with your last step name on Power Query.

[sabin_arsenal]

Hi camargos88 , 

Is there a way to do it in DAX?

thanks, 

[camargos88 · Accepted solution]

Hi @sabin_arsenal ,

Try this code:

Column =

VAR _index = 'Table'[Index]

VAR _sw_h_b = 'Table'[SW_H_B]

VAR _p1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index - 1))

VAR _n1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 1))

VAR _n2 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 2))

RETURN IF(_p1 = "Y" && _sw_h_b = "Y" && _n1 = "N" && _n2 = "N"; "Y"; "N")

[sabin_arsenal]

That worked like a charm, many thanks. Dont mind me asking, but would u mind quickly explaining the logic behind the code? VAR function is variance? 

[camargos88]

Hi @sabin_arsenal ,

VAR is a keyword to declare variables in DAX.

Column =

VAR _index = 'Table'[Index] // get the current index

VAR _sw_h_b = 'Table'[SW_H_B] // get the current value for SW_H_B column

VAR _p1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index - 1)) // SW_H_B value for previous row

VAR _n1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 1)) // SW_H_B value for next row

VAR _n2 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 2)) // SW_H_B value for current + 2 row

RETURN IF(_p1 = "Y" && _sw_h_b = "Y" && _n1 = "N" && _n2 = "N"; "Y"; "N") comparing the values

The distinct function plays the important role here, it returns the scalar value for the search.

I hope I made it clear, let me know if no.

[sabin_arsenal]

Thank you!

[sabin_arsenal]

will try and let u know, thanks!