Forum Discussion
Resolver IIUnique 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 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 |
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/zkNkoE4gdTOvgLKCAnnual 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 ) )
12 Replies
- Jihwan_Kim
Super User
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) )
yasbosJihwan_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
Jihwan_Kim I have been making modifications to the date logic in your code -- to no avail 😞
- tamerj1
Community Champion
Hi yasbos
Another way of doing that: https://www.dropbox.com/t/MvlaosDA8p7vw6dDThe 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
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/zkNkoE4gdTOvgLKCAnnual 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 ) )
