Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Power Query
- If and then statement between rows (finding a sequ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

sabin_arsenal

Helper I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
12:27 PM

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 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

camargos88

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
02:14 PM

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 7

Highlighted
##

camargos88

Super User V

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
12:37 PM

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.

Highlighted
##

sabin_arsenal

Helper I

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
01:42 PM

will try and let u know, thanks!

Highlighted
##

sabin_arsenal

Helper I

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
02:04 PM

Highlighted

camargos88

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
02:14 PM

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

Highlighted
##

sabin_arsenal

Helper I

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
05:50 PM

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

camargos88

Super User V

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
06:01 PM

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.

Highlighted
##

sabin_arsenal

Helper I

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2020
06:53 PM

Thank you!