Forum Discussion

Truelearner's avatar
Truelearner
Helper III
6 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Truelearner
Helper III
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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