Forum Discussion
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 -...
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")))))))
Thanks camargos88 , when I was writing the code, I had a feeling about the ".", i redid the variables and it works now, thanks again for your help, appreciate it a lot.