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")))))))
     
     
v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi sabin_arsenal ,

 

[OPEN] INDEX-3 > [CLOSE] INDEX - 1 & [CLOSE] INDEX = 0 THEN "YY"
I don't understand [CLOSE] INDEX = 0, so I treat it as [OPEN] INDEX-3 > [CLOSE] INDEX - 0. It may result in some incorrect results, you could edit the logic as your own understanding.

At first, you need a new index column from 0 and get the value based on this index column.

Then use IF() or SWITCH() to get results.

Here is my test file for your reference.

 

sabin_arsenal's avatar
sabin_arsenal
Helper I
6 years ago

I checked out your file.

 

close index = 0 implies the latest row of data.  Does that help?

 

thanks,