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

ronan_b — Tue, 18 Apr 2023 15:49:22 GMT

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

topic Create a virual table in which a column expression can be filter by a date slicer in DAX Commands and Tips

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