Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
9 | |
8 | |
7 |
User | Count |
---|---|
17 | |
10 | |
7 | |
6 | |
6 |