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

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

    • sabin_arsenal's avatar
      sabin_arsenal
      Helper I
      6 years ago

      camargos88 Here is the code, mind you the column names and such are different from I provided earlier, I simplified it  

       many thanks, 

       

      custom1 =

      VAR _index = EURUSD[Index]

      VAR _SWING_HIGH = EURUSD[SWING_HIGH]

      VAR _Column1.bid.c = EURUSD[Column1.bid.c]

      VAR _C_O = EURUSD[C_O]

      VAR _SWING_HIGH_1 = CALCULATE(DISTINCT(EURUSD[SWING_HIGH]), FILTER(EURUSD,EURUSD[Index] = _index - 1))

      VAR _Column1.bid.o_1 = CALCULATE(DISTINCT(EURUSD[Column1.bid.o]), FILTER(EURUSD, EURUSD[Index] = _index - 1))

      VAR _Column1.bid.c_1 = CALCULATE(DISTINCT(EURUSD[Column1.bid.c]), FILTER(EURUSD, EURUSD[Index] = _index - 1))

      VAR _SWING_HIGH_2 = CALCULATE(DISTINCT(EURUSD[SWING_HIGH]), FILTER(EURUSD, EURUSD[Index] = _index - 2))

      VAR _Column1.bid.o_2 = CALCULATE(DISTINCT(EURUSD[Column1.bid.o]), FILTER(EURUSD, EURUSD[Index] = _index - 2))

      VAR _Column1.bid.c_2 = CALCULATE(DISTINCT(EURUSD[Column1.bid.c]), FILTER(EURUSD, EURUSD[Index] = _index - 2))

      VAR _SWING_HIGH_3 = CALCULATE(DISTINCT(EURUSD[SWING_HIGH]), FILTER(EURUSD, EURUSD[Index] = _index - 3))

      VAR _Column1.bid.o_3 = CALCULATE(DISTINCT(EURUSD[Column1.bid.o]), FILTER(EURUSD, EURUSD[Index] = _index - 3))

      VAR _Column1.bid.c_3 = CALCULATE(DISTINCT(EURUSD[Column1.bid.c]), FILTER(EURUSD, EURUSD[Index] = _index - 3))

      RETURN IF(_SWING_HIGH = "N" && _SWING_HIGH_1 = "N" && _SWING_HIGH_2 = "Y",

      IF(EURUSD[C_O] >= 0 && _Column1.bid.o_3 > _Column1.bid.c_1 && _Column1.bid.c = 0, "YY",

      IF(EURUSD[C_O] >= 0 && _Column1.bid.o_3 > _Column1.bid.c_1 && _Column1.bid.c_3 < _Column1.bid.c, "YN",

      IF(EURUSD[C_O] >= 0 && _Column1.bid.o_3 < _Column1.bid.c_1 && _Column1.bid.c_3 > _Column1.bid.c, "NY",

      IF(EURUSD[C_O] < 0 && _Column1.bid.c_3 > _Column1.bid.c_1 && _Column1.bid.c = 0, "YY",

      IF(EURUSD[C_O] < 0 && _Column1.bid.c_3 > _Column1.bid.c_1 && _Column1.bid.c_3 < _Column1.bid.c, "YN",

      IF(EURUSD[C_O] < 0 && _Column1.bid.c_3 < _Column1.bid.c_1 && _Column1.bid.c_3 > _Column1.bid.c, "NY", "N")))))))

       

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Hi sabin_arsenal ,

         

        Try changing your variable names, don't use dot...replace it for "_".