CODEWITHSUNDEEP
powerbidax
emil
emil

Reputation: 25

Count of active rows between two dates, sliced by department

I'm trying to get count of active employees by date that can be filtered by department.

The following is what my Employees data more or less looks like. Additionally, there's a dim_Department table connected to the main Employees table and a TERMINATED(Y/N) column. I found a similar case here (without the departments and terminated(y/n) though):

enter image description here

I used the following DAX expression to get a table of total number of employees (organization-wide), but I'm not sure how to filter this metric using the slicer showing departments.

CountOfActive = 
var _selectedDate = MAX('Calendar'[Date])
return
CALCULATE(COUNTROWS('employee'), filter(ALL(employee), employee[Hire Date] <= VALUE(_selectedDate) && (employee[Termination Date] >= VALUE(_selectedDate) || ISBLANK(employee[Termination Date]))))

CountOfTerminated = 
var _selectedDate = MAX('Calendar'[Date])
return
CALCULATE(COUNTROWS('employee'), filter(ALL(employee), employee[Hire Date] <= VALUE(_selectedDate) && (employee[Termination Date] < VALUE(_selectedDate) )))

How do I get my department slicer to filter the results of this column? Or should I use a different expression, or make a column/table for each department?

Upvotes: 0

Views: 2056

Answers (1)

Umut K
Umut K

Reputation: 1388

we have answered that before i guess...

Unique Count

Unique Count = 
VAR _max =
    MAX ( 'Calendar Table'[Date] )
VAR _min =
    MIN ( 'Calendar Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID ] ),
        'Table'[Date ] <= _max
            && 'Table'[Date ] >= _min
    )

Unique Count (Active)

Unique Count (Active) = 
VAR _max =
    MAX ( 'Calendar Table'[Date] )
VAR _min =
    MIN ( 'Calendar Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID ] ),
        'Table'[Date ] <= _max
            && 'Table'[Date ] >= _min
            && 'Table'[Status] = "Active"
    )

Unique Count (Terminated)

Unique Count (Terminated) = 
VAR _max =
    MAX ( 'Calendar Table'[Date] )
VAR _min =
    MIN ( 'Calendar Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID ] ),
        'Table'[Date ] <= _max
            && 'Table'[Date ] >= _min
            && 'Table'[Status] = "Terminated"
    )

also you can insert your data into that sample file and see if it works...

result

Upvotes: 2

Related Questions