cancel
Showing results for
Search instead 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.

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
Helper I

Thank you!

## Helpful resources

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors