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 )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Active FullName count measure: = 
VAR _mindate =
    MIN ( 'Calendar'[Date] )
VAR _maxdate =
    MAX ( 'Calendar'[Date] )
VAR _activeemptable =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( Data, Data[Code], Data[FULL_NAME] ),
                "@active",
                    CALCULATE (
                        COUNTROWS (
                            FILTER (
                                Data,
                                Data[Start_Date] <= _maxdate
                                    && OR ( Data[End_Date] >= _mindate, Data[End_Date] = BLANK () )
                            )
                        )
                    )
            ),
            [@active] >= 1
        ),
        Data[FULL_NAME]
    )
RETURN
    IF( HASONEVALUE('Calendar'[Year CC] ), COUNTROWS(_activeemptable) )

 

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 😞

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    EmpZ for code10 worked in 2020.

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

      Thanks Jihwan_Kim . The screen that I provided above and below shows that the total for 2020 should be 2. However, the table in your pbix shows 3 for 2020. Thanks so much for the effort.