Forum Discussion

rsbin's avatar
rsbin
Community Champion
1 year ago
Solved

Find Terminated Employees

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,

Need Help
  • ThxAlot's avatar
    ThxAlot
    1 year ago

     

    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

     

7 Replies

  • ThxAlot's avatar
    ThxAlot
    Super User
    1 year ago

     

    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's avatar
      rsbin
      Community Champion
      1 year ago

      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,

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    1 year ago

    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. 

  • rsbin's avatar
    rsbin
    Community Champion
    1 year ago

    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's avatar
      ExcelMonke
      Impactful Individual
      1 year ago

      No problem, have you considered the ALLEXCEPT function? 

      • rsbin's avatar
        rsbin
        Community Champion
        1 year ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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.