Derive the Values using Group or Rank

[FSyed]

Hi

Can someone please assist me how to derive the value for Column "CC_Derive" using the following example

 

Happy to provide the sample data file but not sure how and I can't see the option

EmpNo	Name	Amount	CC	Flag	CC_Derive
123	Mr Smith 100	4652	0	4652
123	Mr Smith 200	4659	0	4659
123	Mr Smith 300	999	1	4659
124	Mr X	400	4971	0	4971
124	Mr X	300	999	1	4971
124	Mr X	200	4834	0	4834
124	Mr X	100	4659	0	4659

[FSyed · Accepted solution]

Hi Alex, 

Thanks for your help appreciate that,  its kind of working but I need to resolve using DAX funtion I guess

After few attempts i think its workingfine 

I have use teh following steps:

Output Example

[AlexisOlson]

I think this would be easiest in the query editor.

• Replace 999 with null.
• Fill down the CC column.

[FSyed]

I have used following function (New Column) to derive the Flag values and Max_Amount

col_Flag_999_998=
var var1 = tbl_Distinct_Employee_CC[CostCentre]
RETURN
If(OR (var1 = 498650, var1 = 498655) , 1 ,BLANK())