Forum Discussion
need help with DAX
I have employee hire dates i want to create the Total Employee column Anonymous cham amitchandak Greg_Deckler Mariusz
| Employee Hire date | Name | DESIGNATION | EMPID | Total Employee |
| 01-mar-2020 | Raj | CEO | 1 | 1 |
| 01-mar-2020 | Raj | CFO | 1 | 1 |
| 06-mar-2020 | Ravi | ANALYST | 2 | 2 |
| 08-mar-2020 | Krish | SENIOR | 3 | 3 |
| 08-mar-2020 | Trini | BA | 4 | 4 |
| 12-Apr-2020 | Shafi | TECHLEAD | 5 | 5 |
HI Truelearner,
I'd like to suggest you add distinct function to remove duplicate rows:
Measure = COUNTROWS ( FILTER ( DISTINCT ( ALLSELECTED ( Table ) ), [Hire Date] <= MAX ( Table[Hire Date] ) ) )Regards,
Xiaoxin Sheng
5 Replies
COUNTROWS(FILTER('Table',[Employee Hire Date] <= EARLIER([Employee Hire Date])))
its almost what i needed but the problem is with duplicates with DATA , can you check my table i posted about once.
HI Truelearner,
If you want total employee count can interact with filters, I'd like to suggest you write a measure formula instead. (calculated column formula not able to interact with filter/slicers)
Measure = COUNTROWS ( FILTER ( ALLSELECTED ( Table ), [Hire Date] <= MAX ( Table[Hire Date] ) ) )Regards,
Xiaoxin Sheng
it works like a charm but with the data i have above it is counting the duplicate rows as well , is there any chance where i can ignore the duplicates like in my case same guy is CEO and CFO but i want to caluclate him as one single record though he has two recrods because of the designation
HI Truelearner,
I'd like to suggest you add distinct function to remove duplicate rows:
Measure = COUNTROWS ( FILTER ( DISTINCT ( ALLSELECTED ( Table ) ), [Hire Date] <= MAX ( Table[Hire Date] ) ) )Regards,
Xiaoxin Sheng
