topic Re: If and then statement between rows (finding a sequence) in Power Query

If and then statement between rows (finding a sequence)

sabin_arsenal — Mon, 11 May 2020 19:28:24 GMT

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

Re: If and then statement between rows (finding a sequence)

camargos88 — Mon, 11 May 2020 19:38:24 GMT

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.

Re: If and then statement between rows (finding a sequence)

sabin_arsenal — Mon, 11 May 2020 20:42:38 GMT

will try and let u know, thanks!

Re: If and then statement between rows (finding a sequence)

sabin_arsenal — Mon, 11 May 2020 21:04:41 GMT

Hi camargos88 ,

Is there a way to do it in DAX?

thanks,

Re: If and then statement between rows (finding a sequence)

camargos88 — Mon, 11 May 2020 21:14:19 GMT

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")

Re: If and then statement between rows (finding a sequence)

sabin_arsenal — Tue, 12 May 2020 00:50:27 GMT

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?

Re: If and then statement between rows (finding a sequence)

camargos88 — Tue, 12 May 2020 01:01:18 GMT

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.

Re: If and then statement between rows (finding a sequence)

sabin_arsenal — Tue, 12 May 2020 01:53:14 GMT

Thank you!