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,

many thanks for your help.

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

Did I answer your question? Mark my post as a solution!

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.

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

I checked out your file.

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

Did I answer your question? Mark my post as a solution!

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.

Did I answer your question? Mark my post as a solution!

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 ?

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors