cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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.

 

IndexSW_H_BComputed value 
0NN
1NN
2NN
3NN
4NN
5YN
6YN
7NN
8YN
9NN
10YN
11NN
12NN
13NN
14YN
15NN
16NN
17NN
18YN
19YN
20YN
21YY
22NN
23NN
24YN
25NN
26NN
27NN
28NN
29NN
30YN
31NN
32YN
33YY
34NN
35NN

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

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

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


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

Proud to be a Super User!



View solution in original post

7 REPLIES 7
Highlighted
Super User V
Super User V

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

Hi @sabin_arsenal ,

 

Try this m code:

Capture.PNG

 

 

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

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

will try and let u know, thanks!

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

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

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


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

Proud to be a Super User!



View solution in original post

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

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

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.


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

Proud to be a Super User!



Highlighted
Helper I
Helper I

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

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