Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ronan_b
Frequent Visitor

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

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] )
        )
)

 

Screenshot 2023-04-18 084258.png

 

Dataset 

Thank you for any help in advance,

Ronan

 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.