Unique Records Based On Employee Name

[yasbos]

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 with the company based on whether they are active with at least one of the company's clients. The example below illustrates this. All six records belong to the same employee, EmpX, shown under the Full_Name column. However, because EmpX provides services to six clients, C1, C2, etc., as shown in the column Code, EmpX appears 6 times with different Start dates (always has a value) and End_Date (which will have a value only if EmpX service to this client has been discontinued.)

What I'm trying to do is to count the number of employees who have been active in each year. The active criterion is that if the employee has been active for at least one client during that year (the start date is equal to or less than the year) then this employee gets counted as active in, say 2020. I managed to do this step by adding the last three columns indicating if the employee was/is active during that year for that client. However, I now need to count the employees that were/are active under each year. If I add the 1s under each of the columns, I will clearly count the same employee multiple times as being active during this year.

In the below example, EmpX should be counted as only 1 under each year, rather than now 5 under 2020, 4 under 2021 and 4 under 2022.

In summary, I need to count the employee as being active for our company (not the client) during a given year. The employee is active for the company if they are active with at least one client. Once that is accomplished, I can then sum up the number of active employees per year in order to produce the total per year of active employees for our company.

Thank you.

code	FULL_NAME	Start_Date	End_Date	ACTIVE_2020	ACTIVE_2021	ACTIVE_2022
-----------	-----------	-----------	-----------	-----------	-----------	-----------
c1	EmpX	2017-04-21	NULL	1	1	1
c2	EmpX	2014-12-24	2021-05-12	1	0	0
c3	EmpX	2013-01-11	NULL	1	1	1
c4	EmpX	2013-05-31	NULL	1	1	1
c5	EmpX	2014-12-24	NULL	1	1	1
c6	EmpX	2017-04-21	2017-04-21	0	0	0

[tamerj1]

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]

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 · Accepted solution]

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]

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.

[tamerj1]

@yasbos 
Yes but the employees from previous years are still active. Aren't they?

[yasbos]

@tamerj1 , Thanks so much. Yes. This seems to be working. Much appreciated.

[yasbos]

@tamerj1  oh my! You are right!! Sorry...in meetings (and in one now) and clearly am not focused. I will review after done in the next hour or so. I'll get back to you in under 2 hours. Thanks so much!

[Jihwan_Kim]

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]

@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!!

[Jihwan_Kim]

Hi,

EmpZ for code10 worked in 2020.

[yasbos]

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. 

[yasbos]

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