Forum Discussion

Alice83's avatar
Alice83
Frequent Visitor
1 year ago
Solved

Call Agent switching Teams

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...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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.

     

  • johnt75's avatar
    johnt75
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.