Forum Discussion

sabin_arsenal's avatar
sabin_arsenal
Helper I
6 years ago
Solved

IF and then statement with a sequence

Hello,    First off, this community is awesome, many thanks to everyone who provide solutions, for someone who is not a coder that is pretty awesome.    I need help to code the following in DAX -...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    Hi sabin_arsenal ,

     

    Try this code:

     

    Column =
    VAR _index = 'Table'[Index]
    VAR _S_HIGH = 'Table'[SWING_HIGH]
    VAR _CLOSE = 'Table'[CLOSE]
    VAR _C_O = 'Table'[C_O]
    VAR _S_HIGH_1 = CALCULATE(DISTINCT('Table'[SWING_HIGH]); FILTER('Table'; 'Table'[Index] = _index - 1))
    VAR _OPEN_1 = CALCULATE(DISTINCT('Table'[OPEN]); FILTER('Table'; 'Table'[Index] = _index - 1))
    VAR _CLOSE_1 = CALCULATE(DISTINCT('Table'[CLOSE]); FILTER('Table'; 'Table'[Index] = _index - 1))
    VAR _S_HIGH_2 = CALCULATE(DISTINCT('Table'[SWING_HIGH]); FILTER('Table'; 'Table'[Index] = _index - 2))
    VAR _OPEN_2 = CALCULATE(DISTINCT('Table'[OPEN]); FILTER('Table'; 'Table'[Index] = _index - 2))
    VAR _CLOSE_2 = CALCULATE(DISTINCT('Table'[CLOSE]); FILTER('Table'; 'Table'[Index] = _index - 2))
    VAR _S_HIGH_3 = CALCULATE(DISTINCT('Table'[SWING_HIGH]); FILTER('Table'; 'Table'[Index] = _index - 3))
    VAR _OPEN_3 = CALCULATE(DISTINCT('Table'[OPEN]); FILTER('Table'; 'Table'[Index] = _index - 3))
    VAR _CLOSE_3 = CALCULATE(DISTINCT('Table'[CLOSE]); FILTER('Table'; 'Table'[Index] = _index - 3))
    RETURN IF(_S_HIGH = "N" && _S_HIGH_1 = "N" && _S_HIGH_2 = "Y";
    IF(_C_O >= 0 && _OPEN_3 > _CLOSE_1 && _CLOSE = 0; "YY";
    IF(_C_O >= 0 && _OPEN_3 > _CLOSE_1 && _CLOSE_3 < _CLOSE; "YN";
    IF(_C_O >= 0 && _OPEN_3 < _CLOSE_1 && _CLOSE_3 > _CLOSE; "NY";
    IF(_C_O < 0 && _CLOSE_3 > _CLOSE_1 && _CLOSE = 0; "YY";
    IF(_C_O < 0 && _CLOSE_3 > _CLOSE_1 && _CLOSE_3 < _CLOSE; "YN";
    IF(_C_O < 0 && _CLOSE_3 < _CLOSE_1 && _CLOSE_3 > _CLOSE; "NY"; "N")))))))
     
     
camargos88's avatar
camargos88
Community Champion
6 years ago

Hi sabin_arsenal ,

 

Try this code:

 

Column =
VAR _index = 'Table'[Index]
VAR _S_HIGH = 'Table'[SWING_HIGH]
VAR _CLOSE = 'Table'[CLOSE]
VAR _C_O = 'Table'[C_O]
VAR _S_HIGH_1 = CALCULATE(DISTINCT('Table'[SWING_HIGH]); FILTER('Table'; 'Table'[Index] = _index - 1))
VAR _OPEN_1 = CALCULATE(DISTINCT('Table'[OPEN]); FILTER('Table'; 'Table'[Index] = _index - 1))
VAR _CLOSE_1 = CALCULATE(DISTINCT('Table'[CLOSE]); FILTER('Table'; 'Table'[Index] = _index - 1))
VAR _S_HIGH_2 = CALCULATE(DISTINCT('Table'[SWING_HIGH]); FILTER('Table'; 'Table'[Index] = _index - 2))
VAR _OPEN_2 = CALCULATE(DISTINCT('Table'[OPEN]); FILTER('Table'; 'Table'[Index] = _index - 2))
VAR _CLOSE_2 = CALCULATE(DISTINCT('Table'[CLOSE]); FILTER('Table'; 'Table'[Index] = _index - 2))
VAR _S_HIGH_3 = CALCULATE(DISTINCT('Table'[SWING_HIGH]); FILTER('Table'; 'Table'[Index] = _index - 3))
VAR _OPEN_3 = CALCULATE(DISTINCT('Table'[OPEN]); FILTER('Table'; 'Table'[Index] = _index - 3))
VAR _CLOSE_3 = CALCULATE(DISTINCT('Table'[CLOSE]); FILTER('Table'; 'Table'[Index] = _index - 3))
RETURN IF(_S_HIGH = "N" && _S_HIGH_1 = "N" && _S_HIGH_2 = "Y";
IF(_C_O >= 0 && _OPEN_3 > _CLOSE_1 && _CLOSE = 0; "YY";
IF(_C_O >= 0 && _OPEN_3 > _CLOSE_1 && _CLOSE_3 < _CLOSE; "YN";
IF(_C_O >= 0 && _OPEN_3 < _CLOSE_1 && _CLOSE_3 > _CLOSE; "NY";
IF(_C_O < 0 && _CLOSE_3 > _CLOSE_1 && _CLOSE = 0; "YY";
IF(_C_O < 0 && _CLOSE_3 > _CLOSE_1 && _CLOSE_3 < _CLOSE; "YN";
IF(_C_O < 0 && _CLOSE_3 < _CLOSE_1 && _CLOSE_3 > _CLOSE; "NY"; "N")))))))
 
 
sabin_arsenal's avatar
sabin_arsenal
Helper I
6 years ago

camargos88 did the code work at your end? i doubled checked all the lines and they look fine at my end but still the error. 

 

thanks 

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    sabin_arsenal ,

     

    The code worked here, I just replaced the "." by "," when I loaded it.

    • sabin_arsenal's avatar
      sabin_arsenal
      Helper I
      6 years ago

      camargos88 

       

      Can I ask where this  "." is in the code? 

       

      the error does say "." syntax is incorrect, but i am having a hard time finding that ".".