Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to Solution.
Hi @sabin_arsenal ,
Try this code:
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.
I checked out your file.
close index = 0 implies the latest row of data. Does that help?
thanks,
Hi @sabin_arsenal ,
Try this code:
@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
The code worked here, I just replaced the "." by "," when I loaded it.
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 ".".
Can you share your pbix ?
@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")))))))
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.
thanks for this @camargos88 , i am getting 'The syntax for '.' is incorrect.' the logic is sound in the code though.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
17 | |
11 | |
9 | |
8 |
User | Count |
---|---|
42 | |
24 | |
21 | |
13 | |
12 |