need help with DAX

[Truelearner]

I have employee hire dates i want to create the Total Employee column  @mgwena @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

[v-shex-msft]

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

[Truelearner]

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

[v-shex-msft · Accepted solution]

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

[Greg_Deckler]

COUNTROWS(FILTER('Table',[Employee Hire Date] <= EARLIER([Employee Hire Date])))

[Truelearner]

its almost what i needed but the problem is with duplicates with DATA , can you check my table i posted about once.