Forum Discussion
yasbos
Resolver II
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 ...
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 ) )
yasbosResolver II
Resolver IIThanks, 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.
