Call Agent switching Teams

Hi Alice83 ,

To properly handle the requirement of filtering agents based on their active periods and dynamically displaying their respective team data, we first need to ensure that a disconnected calendar table is used. This is crucial because the fact table contains two date fields, Start Date and End Date. If the calendar table were directly related to the fact table, it would only filter one date field, leading to incorrect results when evaluating date ranges.

A disconnected calendar table can be created using the following DAX formula:

DisconnectedCalendar = CALENDAR(DATE(2023, 1, 1), DATE(2030, 12, 31))

This table serves as a standalone calendar that is not connected to the fact table, allowing greater flexibility in calculations involving date ranges. Once the disconnected calendar is created, it can be used in slicers for year or date selection.

Next, we need to dynamically calculate whether an agent is active within the selected date or year. If the goal is simply to filter out inactive agents, we can use a calculated column to determine if an agent’s Start Date and End Date fall within the selected year. The column can be defined as follows:

ActiveForSlicer =
VAR SelectedYear = MAX('DisconnectedCalendar'[Year])
RETURN
    'YourTable'[Start Date] <= DATE(SelectedYear, 12, 31)
        && 'YourTable'[End Date] >= DATE(SelectedYear, 1, 1)

This column evaluates whether an agent was active during the selected year and can be used to filter the slicer by setting the condition to ActiveForSlicer = TRUE. Agents who were inactive during the selected year will be excluded.

If a more dynamic approach is needed, such as responding to a slicer that allows selection of specific dates, a measure should be used instead. The following measure dynamically calculates whether an agent is active:

IsActiveAgent =
VAR SelectedYear = MAX('DisconnectedCalendar'[Year])
RETURN
    IF (
        CALCULATE (
            COUNTROWS('YourTable'),
            'YourTable'[Start Date] <= DATE(SelectedYear, 12, 31),
            'YourTable'[End Date] >= DATE(SelectedYear, 1, 1)
        ) > 0,
        1,
        0
    )

This measure checks whether an agent’s active period overlaps with the selected year and can be used as a filter on visuals where only active agents need to be displayed (IsActiveAgent = 1).

For displaying the correct team data, the CurrentTeam measure combines this logic and dynamically assigns agents to the appropriate team based on the selected date. This measure ensures that agents are assigned to the team they were part of during the selected period:

CurrentTeam =
VAR SelectedDate = MAX('DisconnectedCalendar'[Date])
RETURN
    IF (
        CALCULATE (
            COUNTROWS('YourTable'),
            'YourTable'[Start Date] <= SelectedDate &&
            'YourTable'[End Date] >= SelectedDate
        ) > 0,
        CALCULATE (
            MAX('YourTable'[Team]),
            'YourTable'[Start Date] <= SelectedDate &&
            'YourTable'[End Date] >= SelectedDate
        ),
        BLANK()
    )

The CurrentTeam measure dynamically evaluates whether an agent is active during the selected date and assigns the corresponding team. For agents who are no longer active, the measure returns BLANK(), effectively excluding them from visuals.

By using a disconnected calendar table, the flexibility of filtering across both Start Date and End Date is preserved. Additionally, combining the logic into measures or calculated columns ensures accurate filtering and team assignments, tailored to the selected period. This approach avoids any conflicts that may arise from direct relationships in the data model and provides a clear way to handle agents switching teams or leaving the organization entirely.

Best regards,

Hi Johnt75

Your solution works perfectly for the Agent Name slicer. When I switch years only the still employeed agents appear.

Now I have a problem that this measure does not apply to all the other visuals meaning the values of the calls. How can I solve this? As you can see Istvan does not appear in the Agent Names for year 2023 but his calls are counted as the Filter or the measure does not apply to the number of calls.

Regards

Hi,

Thanks for the solution johnt75  and DataNinja777  offered, and i want to offer some more infotmation for user to refer to.

hello Alice83 , you can refer to the following solution.

Sample data 

And there is a calendar table, and there is a 1:n relationship between the tables(calendar:date->table:start date)

Creaet a measure

MEASURE =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        OR (
            'Table'[Start date] < MAX ( 'Calendar'[Date] )
                && 'Table'[End date] > MIN ( 'Calendar'[Date] ),
            ISBLANK ( 'Table'[End date] )
        ),
        CROSSFILTER ( 'Calendar'[Date], 'Table'[Start date], NONE )
    )
RETURN
    _count

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.