Create a virual table in which a column expression can be filter by a date slicer

[ronan_b]

Hi all,

I am repharsing a previous query with the hope of getting some help.
In the Employee Data table I have a EmpID, Date of Hire, and Date of Termination columns. The relationship between 'Employee Data'[Date of Hire] and 'dimCalender'[date] is active, the relationship between 'Employee Data'[Date of Termination] and 'dimCalender'[date] is inactive.

I want to create a virtual table with columns for employee ID, tenure, and tenure category. I want this table to be filtered by a date slicer and the tenure category in the virtual table to be added to the x-axis of a visual. The issue I am having is that the "Tenure" column of the virtual table is not filtering when the date slicer is adjusted.

Here are the measures I am using:

Tenure Measure (Filtered) =
VAR StartDate =
    CALCULATE ( 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
    )

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 =
SELECTCOLUMNS (
    FILTER (
        'Employee Data',
        'Employee Data'[Date of Hire] <= MAX ( dimCalendar[Date] )
            && (
                ISBLANK ( 'Employee Data'[Date of Termination] )
                    || 'Employee Data'[Date of Termination] > MAX ( dimCalendar[Date] )
            )
    ),
    "Employee ID", 'Employee Data'[EmpID],
    "Tenure",
        CALCULATE (
            [Tenure Measure (Filtered)],
            ALLEXCEPT ( 'Employee Data', 'Employee Data'[EmpID] )
        ),
    "Tenure Category",
        CALCULATE (
            [Tenure Category],
            ALLEXCEPT ( 'Employee Data', 'Employee Data'[EmpID] )
        )
)

Dataset 

Thank you for any help in advance,

Ronan