Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
23 | |
18 | |
12 |