cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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 -

<Code is looking for a sequence in [SWING_HIGH] , sequence is N,N,Y>

if [Swing_HIGH]

INDEX 0 = N
INDEX - 1 = N
INDEX - 2 = Y

& <looking for coditions, [C_O] drives the logic>

IF [C_O] = > 0,

[OPEN] INDEX-3 > [CLOSE] INDEX - 1 & [CLOSE] INDEX = 0 THEN "YY"

[OPEN] INDEX-3> [CLOSE] INDEX - 1 & CLOSE INDEX - 3< INDEX = 0 THEN "YN"

[OPEN] INDEX-3<[CLOSE] INDEX - 1 & CLOSE INDEX - 3>INDEX = 0 THEN "NY"

OR

IF [C_O] < 0

[CLOSE] INDEX - 3 > [CLOSE] INDEX - 1 & [CLOSE] INDEX = 0 THEN "YY"

[CLOSE] INDEX-3> [CLOSE] INDEX - 1 & [CLOSE] INDEX - 3< INDEX = 0 THEN "YN"

[CLOSE] INDEX-3<[CLOSE] INDEX - 1 & [CLOSE] INDEX - 3>INDEX = 0 THEN "NY"

else N

following is the table of date, I didnt fill in the 'N' values,

 OPEN HIGH LOW CLOSE C_O Index SWING_HIGH Expected Value 1.08316 1.08365 1.08168 1.0834 2.4 18 N 1.08338 1.08391 1.08282 1.0837 3.2 19 N 1.08372 1.08454 1.08334 1.08447 7.5 20 N 1.08446 1.08498 1.08405 1.08495 4.9 21 Y 1.08494 1.08497 1.08446 1.08472 -2.2 22 N 1.0847 1.08477 1.08438 1.08466 -0.4 23 N N 1.08468 1.0849 1.08458 1.08461 -0.7 24 N 1.08459 1.08467 1.08398 1.08408 -5.1 25 N 1.08408 1.08467 1.08359 1.08416 0.8 26 N 1.08416 1.08442 1.08258 1.08335 -8.1 27 N 1.08337 1.08362 1.0808 1.08172 -16.5 28 N 1.08172 1.08273 1.08097 1.08241 6.9 29 N 1.08239 1.08294 1.08145 1.08183 -5.6 30 N 1.08185 1.08221 1.0812 1.08138 -4.7 31 N 1.08138 1.08252 1.08137 1.08197 5.9 32 N 1.08196 1.08433 1.08195 1.08322 12.6 33 Y 1.08325 1.08358 1.0813 1.08239 -8.6 34 N 1.0824 1.08262 1.08021 1.08166 -7.4 35 N N 1.08166 1.08166 1.08063 1.08076 -9 36 N 1.08075 1.08115 1.08061 1.08105 3 37 N 1.08105 1.08108 1.0803 1.08088 -1.7 38 N 1.08087 1.08168 1.08072 1.08141 5.4 39 N 1.08141 1.08159 1.07994 1.08046 -9.5 40 N 1.08033 1.08043 1.08024 1.08034 0.1 41 N 1.08037 1.08097 1.08037 1.08092 5.5 42 N 1.08089 1.08101 1.08069 1.08086 -0.3 43 Y 1.08084 1.08097 1.07838 1.07917 -16.7 44 N 1.07919 1.07951 1.07872 1.07942 2.3 45 N YY 1.07941 1.08002 1.07923 1.07995 5.4 46 N 1.07996 1.08066 1.07976 1.08062 6.6 47 N 1.08061 1.08082 1.07998 1.0804 -2.1 48 N 1.08039 1.08166 1.07995 1.08091 5.2 49 N 1.0809 1.08238 1.08029 1.08201 11.1 50 N 1.08199 1.08271 1.08056 1.08154 -4.5 51 Y 1.08155 1.08264 1.08141 1.08196 4.1 52 N 1.08195 1.08239 1.08119 1.0819 -0.5 53 N N 1.08189 1.0836 1.08153 1.08342 15.3 54 N 1.08343 1.08482 1.08328 1.08481 13.8 55 N 1.08482 1.08636 1.08399 1.08589 10.7 56 N 1.08587 1.08845 1.08564 1.08768 18.1 57 Y 1.08769 1.08783 1.08607 1.08742 -2.7 58 N 1.0874 1.08772 1.08615 1.08622 -11.8 59 N N 1.08623 1.08656 1.08473 1.08502 -12.1 60 N 1.08503 1.08576 1.08467 1.08515 1.2 61 N 1.08512 1.08576 1.08498 1.08528 1.6 62 N 1.08527 1.08558 1.08477 1.08498 -2.9 63 N 1.08497 1.08511 1.08461 1.08464 -3.3 64 N 1.0846 1.08467 1.08444 1.08459 -0.1 65 N 1.0846 1.08511 1.0846 1.08509 4.9 66 N 1.08507 1.08509 1.08418 1.08445 -6.2 67 N 1.08444 1.08566 1.0844 1.08543 9.9 68 N 1.08545 1.08561 1.08506 1.0854 -0.5 69 N 1.08538 1.08554 1.08466 1.08501 -3.7 70 N 1.085 1.08517 1.08467 1.08502 0.2 71 N 1.08501 1.08501 1.0843 1.08488 -1.3 72 N 1.08487 1.08535 1.08441 1.08484 -0.3 73 N 1.08485 1.08552 1.0843 1.08444 -4.1 74 Y 1.08442 1.08499 1.083 1.08376 -6.6 75 N 1.08377 1.08451 1.08347 1.08418 4.1 76 N YY 1.08419 1.0847 1.08343 1.08421 0.2 77 N 1.08421 1.0865 1.08397 1.08646 22.5 78 N 1.08647 1.0878 1.08597 1.08758 11.1 79 N 1.08761 1.08802 1.08706 1.08786 2.5 80 N 1.08785 1.08957 1.08527 1.08607 -17.8 81 Y 1.08608 1.08645 1.08362 1.08378 -23 82 N 1.08379 1.08458 1.08297 1.08354 -2.5 83 N YY 1.08355 1.08385 1.0819 1.08215 -14 84 N 1.08215 1.08217 1.08115 1.08171 -4.4 85 N 1.08169 1.08232 1.08144 1.08147 -2.2 86 N 1.08146 1.082 1.0811 1.08143 -0.3 87 N 1.08141 1.08184 1.08132 1.08176 3.5 88 N 1.08176 1.08192 1.08144 1.08192 1.6 89 N 1.08192 1.08197 1.08149 1.08176 -1.6 90 N 1.08174 1.08212 1.0816 1.08205 3.1 91 N 1.08206 1.08234 1.08164 1.08209 0.3 92 Y 1.08208 1.08211 1.0808 1.08088 -12 93 N 1.08087 1.08146 1.08083 1.08144 5.7 94 N YY 1.08142 1.08147 1.08066 1.08083 -5.9 95 N 1.08084 1.08095 1.08044 1.08087 0.3 96 N 1.08086 1.08104 1.08038 1.08067 -1.9 97 N 1.08065 1.08125 1.08008 1.08071 0.6 98 N 1.0807 1.08132 1.07928 1.08039 -3.1 99 N 1.0804 1.08198 1.08016 1.08093 5.3 100 Y 1.08096 1.08124 1.08057 1.08079 -1.7 101 N 1.08081 1.08085 1.07828 1.07909 -17.2 102 N NY 1.0791 1.07941 1.07746 1.07887 -2.3 103 N 1.07888 1.07946 1.07742 1.07883 -0.5 104 N 1.07883 1.08006 1.07833 1.07899 1.6 105 N 1.07898 1.08158 1.07884 1.08106 20.8 106 Y 1.08108 1.08145 1.07877 1.0798 -12.8 107 N 1.0798 1.08039 1.07804 1.07818 -16.2 108 N NY 1.07819 1.07902 1.07795 1.07817 -0.2 109 N 1.07816 1.07841 1.07803 1.07821 0.5 110 N

1 ACCEPTED SOLUTION
Community Champion

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

Proud to be a Super User!

11 REPLIES 11
Community Support

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

If this post helps, then please consider Accept it as the solution to help the other members find it.
Helper I

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

thanks,

Community Champion

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

Proud to be a Super User!

Helper I

@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

Community Champion

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

Proud to be a Super User!

Helper I

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

Community Champion

Can you share your pbix ?

Proud to be a Super User!

Helper I

@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")))))))

Community Champion

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

Proud to be a Super User!

Helper I

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.

Helper I

thanks for this @camargos88 , i am getting 'The syntax for '.' is incorrect.' the logic is sound in the code though.