Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |