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")
     
     
camargos88's avatar
camargos88
Community Champion
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")
 
 
  • sabin_arsenal's avatar
    sabin_arsenal
    Helper I
    6 years ago

    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? 

     

    • camargos88's avatar
      camargos88
      Community Champion
      6 years ago

      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.
      • sabin_arsenal's avatar
        sabin_arsenal
        Helper I
        6 years ago

        Thank you!