Find Terminated Employees

[rsbin]

Good Day Folks,

Looking for some guidance on this one.  Have the following Employee Data Table:

EmployeeID	PointinTimeID	Status	StartDate	EndDate	Terminated Flag
1	4840	Y	20180827	20191121	1
1	6993	Y	20191122	20210813	1
1	11144	Terminated	20210814	20211226	1
1	12018	X	20211227	20241023	1
2	4608	Y	20180827	20200710	0
2	7474	Y	20200711	20201022	0
2	8411	Y	20201029	20210127	0
2	9847	Y	20210128	20211226	0
2	12019	X	20211227	20241023	0
3	4943	Terminated	20180827	20211226	1
3	12020	X	20211227	20241023	1

Looking to create a Calculated Column to identify Terminated Employees.  So for every [EmployeeID] would like to set the Flag to 1 if "Terminated" appears in the [Status] column. 

Please note that I am working in Analysis Services so don't have access to certain DAX functions like SELECTEDVALUE as well as others.

Appreciate any help or guidance.

Kind Regards,

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[EmployeeID]=EARLIER(Data[EmployeeID])&&Data[Status]="Terminated"))>0,1,0)

Hope this helps.

[ThxAlot · Accepted solution]

Flag =
VAR __id = EMPL[EmployeeID]
RETURN
    CALCULATE(
        NOT ISEMPTY( EMPL ),
        ALL(),
        EMPL[EmployeeID] = __id,
        EMPL[Status] = "Terminated"
    ) + 0

Flag =
CALCULATE(
    NOT ISEMPTY( EMPL ),
    ALLEXCEPT( EMPL, EMPL[EmployeeID] ),
    EMPL[Status] = "Terminated"
) + 0

[rsbin]

@ThxAlot ,

Brilliant....Thanks Much!!!

I think this works....need to test it on a much larger and complex dataset, but appreciate the novel approach.

Best Regards,

[rsbin]

@ExcelMonke ,

Thanks for the Reply and apologies for the confusion.  The [TerminatedFlag] column is what I am looking for.

The values I entered in red is the final result that should be obtained.

Regards,

[ExcelMonke]

No problem, have you considered the ALLEXCEPT function? 

[rsbin]

@ExcelMonke ,

Yes, I have considered the ALLEXCEPT function.
But haven't been able to successfully get a complete DAX expression to work the way I need it to.

[ExcelMonke]

Hello,

What exactly seems to be the issue you are trying to solve? The terminated flag looks like it is behaving correctly. 

If this is not the example from a PBIX table, and you are looking for suggestions, you could consider using the ALLEXCEPT function if this is available to you.