Conditionally flag some columns based on group of data

[Anonymous]

Hi,

I have a dataset that looks like this:

Product ID	Dealer	Model	DealerModelKey	CreationMonth	Expected First Flag	MainMonth	Expected Final Flag
abc123	Y121	226	Y121226	201904	0	201904	1
abc124	Y121	226	Y121226	201904	0	201902	1
abc125	Y121	226	Y121226	201903	0	201912	1
abc126	Y121	226	Y121226	201904	0	201701	0 (because 201701 is older than 201902)
abc127	Y121	226	Y121226	201902	1(because creation month is oldest)	201912	1
abc128	A100	123	A100123	201912	0	201807	0
abc129	A100	123	A100123	201904	0	201808	0
abc130	A100	123	A100123	201809	1	201808	0
abc131	A100	900	A100900	201904	0	201903	1
abc132	A100	900	A100900	201807	1	201904	1
abc133	A100	900	A100900	201912	0	201902	1

Problem Statement: For each dealer and model combination(DealerModelKey),
I want to create 2 flag columns in Dax:

Expected First Flag: For a group of DealerModelKey, find the oldest CreationMonth. Call it 1 and all others as 0.
E.g. Since Y121226 has 201902 as the oldest date it is made to 1 and all others as 0.

Expected Final Flag: Compare Expected first flag and MainMonth column. All months older than Expected first flag i.e when Expected first flag = 1, then all months older than that month should be made 0 else 1.

Thanks

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create two columns like DAX below.

Expected First Flag1 = var d=CALCULATE(MIN('Table'[CreationMonth]),ALLEXCEPT('Table','Table'[DealerModelKey]))
return IF('Table'[CreationMonth]=d,1,0)
 
Expected First Flag2 = var d=CALCULATE(MIN('Table'[CreationMonth]),ALLEXCEPT('Table','Table'[DealerModelKey]))
return IF('Table'[MainMonth]>=d,1,0)

Result:

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Amy,

Thanks for the reply. Could you also please explain how the calculation works

Also, my if statement is not accepting non-measure value i.e CreationMonth

It is a whole number in my dataset.

[v-xicai]

Hi @Anonymous ,

The formulas above are created using calculated column instead of measure.

Best Regards,

Amy