Forum Discussion
If and then statement between rows (finding a sequence)
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 |
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")
7 Replies
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.
will try and let u know, thanks!
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")
