Forum Discussion

yasbos's avatar
yasbos
Icon for Resolver II rankResolver II
4 years ago
Solved

Unique Records Based On Employee Name

Greetings to everyone 🙂 I tried to solve this below problem in SQL (my preference), but I couldn't, so I'm now trying to solve it in DAX. There is a scenario where an employee is considered active ...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    HI yasbos 
    I transformed the same formula to another one working at year level only and with simple subtraction ( - 1 ) we can get the desired result. This solution is faster in terms of performance but it works only at year level not date not week not month. https://www.dropbox.com/t/zkNkoE4gdTOvgLKC

    Annual Active Employees = 
VAR CurrentPeriod = 
    VALUES ( 'Date'[Date].[Year] )
RETURN
    SUMX (
        VALUES ( Data[FULL_NAME] ),
        VAR StartDate = 
            YEAR ( CALCULATE ( MIN ( Data[Start_Date] ) ) )
        VAR EndaDateCheck = 
            COUNTROWS ( CALCULATETABLE ( Data, Data[End_Date] <> BLANK ( ) ) ) 
                = COUNTROWS ( CALCULATETABLE ( Data ) )
        VAR EndDate = 
            IF ( EndaDateCheck, YEAR ( CALCULATE ( MAX ( Data[End_Date] ) ) ) - 1, YEAR ( TODAY ( ) ) )
        VAR ServicePeriod =
            GENERATESERIES ( StartDate, EndDate, 1 )
        RETURN 
            IF ( NOT ISEMPTY ( INTERSECT ( CurrentPeriod, ServicePeriod ) ), 1 )
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi yasbos 
Another way of doing that: https://www.dropbox.com/t/MvlaosDA8p7vw6dD

The thing that I don't understand is why you don't want to include EmpZ in 2020 while he was active until Aug. 2020? Please clarify this point and what is your criteria.

Active Employees = 
VAR CurrentPeriod = 
    VALUES ( 'Date'[Date] )
RETURN
    SUMX (
        VALUES ( Data[FULL_NAME] ),
        VAR StartDate = 
            CALCULATE ( MIN ( Data[Start_Date] ) )
        VAR EndaDateCheck = 
            COUNTROWS ( CALCULATETABLE ( Data, Data[End_Date] <> BLANK ( ) ) ) 
                = COUNTROWS ( CALCULATETABLE ( Data ) )
        VAR EndDate = 
            IF ( EndaDateCheck, CALCULATE ( MAX ( Data[End_Date] ) ), TODAY ( ) )
        VAR ServicePeriod =
            CALENDAR ( StartDate, EndDate )
        RETURN 
            IF ( NOT ISEMPTY ( INTERSECT ( CurrentPeriod, ServicePeriod ) ), 1 )
    )
  • yasbos's avatar
    yasbos
    Icon for Resolver II rankResolver II
    4 years ago

    Thanks so much,  tamerj1 . If the year ended and the employee was still active, then I would like to count them. So, if the employee started in 2014 and ended in 2014, then I would like to exclude that from the count. Also, if the employee started in 2013 and ended in, say, 2016, then they were active in 2013, 2014, and 2015. However, I still need to count all of the active employees for the current year (2022) because I need to compare 2022 to the previous years. You see, in my mind, the solution should be easily convertable between incuding the year in which the employee ceased to be active or excluding it. I basically thought/think that it should be just the equality/inequality signs that will need to be adjusted as desired. Thanks again. You tamerj1  and Jihwan_Kim are wonderful.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      HI yasbos 
      I transformed the same formula to another one working at year level only and with simple subtraction ( - 1 ) we can get the desired result. This solution is faster in terms of performance but it works only at year level not date not week not month. https://www.dropbox.com/t/zkNkoE4gdTOvgLKC

      Annual Active Employees = 
VAR CurrentPeriod = 
    VALUES ( 'Date'[Date].[Year] )
RETURN
    SUMX (
        VALUES ( Data[FULL_NAME] ),
        VAR StartDate = 
            YEAR ( CALCULATE ( MIN ( Data[Start_Date] ) ) )
        VAR EndaDateCheck = 
            COUNTROWS ( CALCULATETABLE ( Data, Data[End_Date] <> BLANK ( ) ) ) 
                = COUNTROWS ( CALCULATETABLE ( Data ) )
        VAR EndDate = 
            IF ( EndaDateCheck, YEAR ( CALCULATE ( MAX ( Data[End_Date] ) ) ) - 1, YEAR ( TODAY ( ) ) )
        VAR ServicePeriod =
            GENERATESERIES ( StartDate, EndDate, 1 )
        RETURN 
            IF ( NOT ISEMPTY ( INTERSECT ( CurrentPeriod, ServicePeriod ) ), 1 )
    )
      • yasbos's avatar
        yasbos
        Icon for Resolver II rankResolver II
        4 years ago

        Thanks, tamerj1 . Something seems wrong, though -- unless I am missing something. For example, you have 3 for 2015, when 2015 has only one entry. Similar problems with 2016, etc. They don't seem to meet the criteria.