Forum Discussion
Call Agent switching Teams
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 ResultYou 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.
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 Resultand use that as a page level filter
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
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.
Hi
Do I use both your suggestions together so one on the Agent slicer and the other on the call data visual?
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.
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.
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.