Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
sabin_arsenal
Helper I
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.

 

OPENHIGHLOWCLOSEC_OIndexSWING_HIGHExpected Value
1.083161.083651.081681.08342.418N 
1.083381.083911.082821.08373.219N 
1.083721.084541.083341.084477.520N 
1.084461.084981.084051.084954.921Y 
1.084941.084971.084461.08472-2.222N 
1.08471.084771.084381.08466-0.423NN
1.084681.08491.084581.08461-0.724N 
1.084591.084671.083981.08408-5.125N 
1.084081.084671.083591.084160.826N 
1.084161.084421.082581.08335-8.127N 
1.083371.083621.08081.08172-16.528N 
1.081721.082731.080971.082416.929N 
1.082391.082941.081451.08183-5.630N 
1.081851.082211.08121.08138-4.731N 
1.081381.082521.081371.081975.932N 
1.081961.084331.081951.0832212.633Y 
1.083251.083581.08131.08239-8.634N 
1.08241.082621.080211.08166-7.435NN
1.081661.081661.080631.08076-936N 
1.080751.081151.080611.08105337N 
1.081051.081081.08031.08088-1.738N 
1.080871.081681.080721.081415.439N 
1.081411.081591.079941.08046-9.540N 
1.080331.080431.080241.080340.141N 
1.080371.080971.080371.080925.542N 
1.080891.081011.080691.08086-0.343Y 
1.080841.080971.078381.07917-16.744N 
1.079191.079511.078721.079422.345NYY
1.079411.080021.079231.079955.446N 
1.079961.080661.079761.080626.647N 
1.080611.080821.079981.0804-2.148N 
1.080391.081661.079951.080915.249N 
1.08091.082381.080291.0820111.150N 
1.081991.082711.080561.08154-4.551Y 
1.081551.082641.081411.081964.152N 
1.081951.082391.081191.0819-0.553NN
1.081891.08361.081531.0834215.354N 
1.083431.084821.083281.0848113.855N 
1.084821.086361.083991.0858910.756N 
1.085871.088451.085641.0876818.157Y 
1.087691.087831.086071.08742-2.758N 
1.08741.087721.086151.08622-11.859NN
1.086231.086561.084731.08502-12.160N 
1.085031.085761.084671.085151.261N 
1.085121.085761.084981.085281.662N 
1.085271.085581.084771.08498-2.963N 
1.084971.085111.084611.08464-3.364N 
1.08461.084671.084441.08459-0.165N 
1.08461.085111.08461.085094.966N 
1.085071.085091.084181.08445-6.267N 
1.084441.085661.08441.085439.968N 
1.085451.085611.085061.0854-0.569N 
1.085381.085541.084661.08501-3.770N 
1.0851.085171.084671.085020.271N 
1.085011.085011.08431.08488-1.372N 
1.084871.085351.084411.08484-0.373N 
1.084851.085521.08431.08444-4.174Y 
1.084421.084991.0831.08376-6.675N 
1.083771.084511.083471.084184.176NYY
1.084191.08471.083431.084210.277N 
1.084211.08651.083971.0864622.578N 
1.086471.08781.085971.0875811.179N 
1.087611.088021.087061.087862.580N 
1.087851.089571.085271.08607-17.881Y 
1.086081.086451.083621.08378-2382N 
1.083791.084581.082971.08354-2.583NYY
1.083551.083851.08191.08215-1484N 
1.082151.082171.081151.08171-4.485N 
1.081691.082321.081441.08147-2.286N 
1.081461.0821.08111.08143-0.387N 
1.081411.081841.081321.081763.588N 
1.081761.081921.081441.081921.689N 
1.081921.081971.081491.08176-1.690N 
1.081741.082121.08161.082053.191N 
1.082061.082341.081641.082090.392Y 
1.082081.082111.08081.08088-1293N 
1.080871.081461.080831.081445.794NYY
1.081421.081471.080661.08083-5.995N 
1.080841.080951.080441.080870.396N 
1.080861.081041.080381.08067-1.997N 
1.080651.081251.080081.080710.698N 
1.08071.081321.079281.08039-3.199N 
1.08041.081981.080161.080935.3100Y 
1.080961.081241.080571.08079-1.7101N 
1.080811.080851.078281.07909-17.2102NNY
1.07911.079411.077461.07887-2.3103N 
1.078881.079461.077421.07883-0.5104N 
1.078831.080061.078331.078991.6105N 
1.078981.081581.078841.0810620.8106Y 
1.081081.081451.078771.0798-12.8107N 
1.07981.080391.078041.07818-16.2108NNY
1.078191.079021.077951.07817-0.2109N 
1.078161.078411.078031.078210.5110N 

 

@camargos88 

 

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

Hi @sabin_arsenal ,

 

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!



View solution in original post

11 REPLIES 11
v-eachen-msft
Community Support
Community Support

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.

 

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

I checked out your file.

 

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

 

thanks, 

camargos88
Community Champion
Community Champion

Hi @sabin_arsenal ,

 

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!



@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 

@sabin_arsenal ,

 

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!



@camargos88 

 

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

@sabin_arsenal ,

 

Can you share your pbix ?



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

Proud to be a Super User!



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

 

Hi @sabin_arsenal ,

 

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!



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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors