IF and then statement with a sequence

[sabin_arsenal]

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

@camargos88 

[v-eachen-msft]

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.

[sabin_arsenal]

I checked out your file.

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

thanks, 

[camargos88 · Accepted solution]

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

[sabin_arsenal]

@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 

[camargos88]

@sabin_arsenal ,

The code worked here, I just replaced the "." by "," when I loaded it.

[sabin_arsenal]

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

[camargos88]

@sabin_arsenal ,

Can you share your pbix ?

[sabin_arsenal]

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

[camargos88]

Hi @sabin_arsenal ,

Try changing your variable names, don't use dot...replace it for "_".

[sabin_arsenal]

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.

[sabin_arsenal]

thanks for this @camargos88 , i am getting 'The syntax for '.' is incorrect.' the logic is sound in the code though.