Creating a visual showing Employees by Tenure category filtered by a date slicer

[ronan_b]

HR dataset 

Hello all,

I am a newbie to power bi and am very grateful for all the great infomation available here. I am hoping someone can help me with a problem I am having while working on a project for my portfolio. 
I am using a HR dataset and would like to create a visual showing employees tenure in an area chart filtered by a date slicer. Within the Query editor I created a Tenure column from the hire and termination date coloumns, and also a dimension table of tenure bin categories from an added conditional column.
This works to show employees by tenure category when no date slicer is applied. However, when the end date is lowered I can only get the visual to show the total tenure in the categories instead of the filtered tenure. 

What I am trying to do is create a measure/calculated column of the tenure bin categories that is conditional on the filtered tenure. Then this measure can be added to the x-axis of the visual.

Here are the measures I am using currently:

Total Employees = 
COUNTROWS(
    DISTINCT(
        'Employee Data'
    )
)


Total Employees Active = 
CALCULATE (
    [Total Employees],
    (
        ISBLANK ( 'Employee Data'[Date of Termination] )
            || 'Employee Data'[Date of Termination] > MAX ( dimCalendar[Date] )
                && (
                    (
                        'Employee Data'[Date of Hire] <= MAX ( dimCalendar[Date] )
                            && ( 'Employee Data'[Date of Hire] >= MIN ( dimCalendar[Date] ) )
                    )
                )
    )
)
+0


Tenure Measure (Filtered) = 
VAR StartDate = MIN(DimCalendar[Date])
VAR EndDate = MAX(DimCalendar[Date])
RETURN
SUMX (
    FILTER (
        'Employee Data',
        'Employee Data'[Date of Hire] <= EndDate &&
        (ISBLANK('Employee Data'[Date of Termination]) || 'Employee Data'[Date of Termination] >= StartDate)
    ),
    DATEDIFF (
        'Employee Data'[Date of Hire],
        IF (
            (ISBLANK ( 'Employee Data'[Date of Termination]) || 'Employee Data'[Date of Termination] >= EndDate),
            EndDate,
            'Employee Data'[Date of Termination]
        ),
        DAY
    ) / 365.2425
)

I did try to create a table with the tenure categories as a column, however, I am getting the a messge stating -
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Here are the measures I used.

Tenure Category = 
SWITCH(
    TRUE(),
    [Tenure Measure (Filtered)] < 0.5, "6 months",
    [Tenure Measure (Filtered)] < 1, "1 year",
    [Tenure Measure (Filtered)] < 2, "2 years",
    [Tenure Measure (Filtered)] < 5, "5 years",
    [Tenure Measure (Filtered)] < 10, "10 years",
    [Tenure Measure (Filtered)] >= 10, "10 years or more",
    BLANK()
)

Tenure Bins Table = 
    SUMMARIZE (
        FILTER (
            'Employee Data',
            'Employee Data'[Date of Hire] <= MAX(DimCalendar[Date]) &&
            (ISBLANK('Employee Data'[Date of Termination]) || 'Employee Data'[Date of Termination] >= MIN(DimCalendar[Date]))
        ),
        'Employee Data'[EmpID],
        "Tenure", [Tenure Measure (Filtered)],
        "Tenure Category", [Tenure Category]
    )

Any help on this would be greatly appreciated.

Thank you,
Ronan