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 )
    )
yasbos's avatar
yasbos
Icon for Resolver II rankResolver II
4 years ago

Jihwan_Kim Thanks so much! There just seems to be a tiny glitch there. for example, 2020 active employees should be 2, but the table is shown 3. Regardless, I really appreciate your work. This is great!!

 

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

Jihwan_Kim I have been making modifications to the date logic in your code -- to no avail 😞