Forum Discussion

sabin_arsenal's avatar
sabin_arsenal
Helper I
6 years ago
Solved

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 ...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

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