Call Agent switching Teams

[Alice83]

Hello everyone

I am measuring different call data from two teams, 1st and 2nd level. I have Agents that changed teams and I need to show the respective data.

Agent A was in Team 1st until end of 2024 and now in Team 2nd. I have a table with start and end date, Agent A appears in two lines since he switched team.

Other Agents that left completely I would like to not have them in the slicer anymore if this year is selected.

I am having difficulty to find the correct measure.

SlicersAgent ID table

[johnt75 · Accepted solution]

You can create a measure to use as a filter on the slicer visible. 

The basic pattern is something like

Agent is visible =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR AgentStartDate =
    SELECTEDVALUE ( 'Agent'[Start date] )
VAR AgentEndDate =
    SELECTEDVALUE ( 'Agent'[End Date] )
VAR Result =
    IF (
        AgentStartDate <= MinDate
            && (
                AgentEndDate >= MaxDate
                    || ISBLANK ( AgentEndDate )
            ),
        1,
        0
    )
RETURN
    Result

You may want to tweak the logic a bit to handle cases where someone leaves part way through the year. As it is they would still be included, but you might want to exclude them.

Add the measure to the slicer as a filter, set to only show when the value is 1.

[Anonymous]

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.

[Alice83]

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

[johnt75]

I would create a calculation group with a calculation item which can restrict the agents to only those which should be included.

Something like

Only visible agents =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR VisibleAgents =
    SELECTCOLUMNS (
        FILTER (
            'Agent',
            'Agent'[Start date] <= MinDate
                && (
                    'Agent'[End Date] >= MaxDate
                        || ISBLANK ( AgentEndDate )
                )
        ),
        'Agent'[Agent ID]
    )
VAR Result =
    CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( VisibleAgents ) )
RETURN
    Result

You may be able to apply this as a page level filter, but I'm not sure how it would react with the filter already on the slicer. You may need to apply it to individual visuals.

[Alice83]

Hi johnt75

I am able to add the measure agent is visible to the slicer as well as the column chart and when changing the date de names and number of calls seem to be correct.

The measure only visible agents is not returning anything, not sure when adding to the visual what I am supposed to add like is 1?

Is there a possibilty to add the measures to the whole page? At the moment it's not working or I don't know how to change the measure.

[johnt75]

Only visible agents isn't supposed to be a measure, its a calculation item in a calculation group. Create a new calculation group and then add that code as the calculation item. You should then be able to add the calculation group as a filter at any level ( visual, page, report ) and select the only visible agents calculation item as the option.

[Alice83]

I was able to create the calculation item as a page filter. When selected only active agents are shown in the agent name slicer. It does not however affect the call data. 

For now I need to add your first suggested measure to each visual.

[Alice83]

Hi 

Do I use both your suggestions together so one on the Agent slicer and the other on the call data visual?

[johnt75]

Yes, I think that should work.

[Alice83]

HI

How can I convert or use Agent is visible the measure as a page filter? 

[johnt75 · Accepted solution]

You could create another calculation item like

Agent is visible calculation item =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR AgentStartDate =
    SELECTEDVALUE ( 'Agent'[Start date] )
VAR AgentEndDate =
    SELECTEDVALUE ( 'Agent'[End Date] )
VAR Result =
    IF (
        AgentStartDate <= MinDate
            && (
                AgentEndDate >= MaxDate
                    || ISBLANK ( AgentEndDate )
            ),
        SELECTEDMEASURE ()
    )
RETURN
    Result

and use that as a page level filter

[Alice83]

Hi

Thank you for the calculation item. It is working on some visuals but not on all of my data. I will think on it.

[DataNinja777]

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,

[Alice83]

Hello 

I added your suggestion for the disconnected calendar as a measure if that was correct and then tried to write the second measure but it is giving me an error on the disconnected calendar.

Also I do do have a separate Calendar that I am using as I have data from different tables, call data and tickets and they all have their own dates. 

The Start date and end date relationship to the calendar table is inactive so that I could use it if needed with userelationship. Could it work with my own calendar?

[johnt75 · Accepted solution]

You can create a measure to use as a filter on the slicer visible. 

The basic pattern is something like

Agent is visible =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR AgentStartDate =
    SELECTEDVALUE ( 'Agent'[Start date] )
VAR AgentEndDate =
    SELECTEDVALUE ( 'Agent'[End Date] )
VAR Result =
    IF (
        AgentStartDate <= MinDate
            && (
                AgentEndDate >= MaxDate
                    || ISBLANK ( AgentEndDate )
            ),
        1,
        0
    )
RETURN
    Result

You may want to tweak the logic a bit to handle cases where someone leaves part way through the year. As it is they would still be included, but you might want to exclude them.

Add the measure to the slicer as a filter, set to only show when the value is 1.