If value a is between x and y values then "1" else "0"

[Venkatac]

Hi all,

I have Column Age and Values and Actual value. if actual value between age rows ,I want to falg as 1 esle 0.

For example,

Actual value is 2227 and i want to flag as 1 in 50(Age row) and remaing rows should be 0.

Actual value is 2662 and i want to flag as 1 in 80(Age row) and remaing rows should be 0.

1. I created a calculated measures to get respective values for age (i.e 10, 20,30..90)
10 = CALCULATE(SUM(Table[Values]),Table[Age]=10)
20 = CALCULATE(SUM(Table[Values]),Table[Age]=20)
..up to 90

2.Created a calculated column to Flag

IF(Table[Actual value]<=[10],1,
IF(And(Table[Actual value]>[10],Table[Actual value]<=[20]),1,
IF(And(Table[Actual value]>[20],Table[Actual value]<=[30]),1,
IF(And(Table[Actual value]>[30],Table[Actual value]<=[40]),1,
IF(And(Table[Actual value]>[40],Table[Actual value]<=[50]),1,
IF(And(Table[Actual value]>[50],Table[Actual value]<=[60]),1,
IF(And(Table[Actual value]>[60],Table[Actual value]<=[70]),1,
IF(And(Table[Actual value]>[70],Table[Actual value]<=[80]),1,
IF(And(Table[Actual value]>[80],Table[Actual value]<=[90]),1,
IF(Table[Actual value]>[90],1))))))))))

Dax Formula Results				Desired results
Age	Values	Actual value	Flag		Age	Values	Actual value	Flag
10	1792	2227	0		10	1792	2227	0
20	1937	2227	0		20	1937	2227	0
30	2063	2227	0		30	2063	2227	0
40	2140	2227	0		40	2140	2227	0
50	2240	2227	1		50	2240	2227	1
60	2375	2227	1		60	2375	2227	0
70	2512	2227	1		70	2512	2227	0
80	2720	2227	1		80	2720	2227	0
90	3182	2227	1		90	3182	2227	0
Age	Values	Actual value	Flag		Age	Values	Actual value	Flag
10	1792	2662	0		10	1792	2662	0
20	1937	2662	0		20	1937	2662	0
30	2063	2662	0		30	2063	2662	0
40	2140	2662	0		40	2140	2662	0
50	2240	2662	0		50	2240	2662	0
60	2375	2662	0		60	2375	2662	0
70	2512	2662	0		70	2512	2662	0
80	2720	2662	1		80	2720	2662	1
90	3182	2662	1		90	3182	2662	0

What am I doing wrong? Any help would be greatlu appreciated.

Br,

Ram

[MattAllington]

i'm not sure what you are trying to do, but this may help you
https://exceleratorbi.com.au/banding-in-dax/

[Venkatac · Accepted solution]

Hi Matt, Thank you!

By using Earlier function, i got expected results.

Br,

Ram