cancel
Showing results for
Did you mean:
Highlighted Helper I

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

 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User V

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

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 7
Highlighted Super User V

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

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.

Highlighted Helper I

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

will try and let u know, thanks!

Highlighted Helper I

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

Hi camargos88 ,

Is there a way to do it in DAX?

thanks,

Highlighted Super User V

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

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

Highlighted Helper I

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

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?

Highlighted Super User V

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

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.
Highlighted Helper I

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

Thank you!  