Measure with containsstring and filters

[NotMoike]

Hello, im a total rookie when it comes to PBI and im stuck on a problem i can't find the logic to why it does what it does.

Im trying to model data from our ticketsystem and its a single table (Data) with no relationships.

Date = when the ticket was closed (hierarchy with Year, month, weekNR, day)

TicketID = unique value in numberformat

Agent = The agent who closed the ticket (An Agent can exist in multiple groups)

ActiveAgents = shows the flow on which agent handled the ticket. So in the ticket "123" the logic is that Sam from 1stline did something to the ticket and then Frank from "DWP" did something else and Ted closed the ticket. (Ted is a member of DWP). 

Group = The group who closed the ticket. 

ActiveGroup = Same logic as ActiveAgents, but with the group instead.

Date	TicketID	Agent	ActiveAgents	Group	ActiveGroup
2023-01-01	123	Ted	Sam, Frank	DWP	1stline, DWP
2023-01-01	124	Frank	Frank, Mike, Jules, Ted, Mike, Ted	DWP	DWP
2023-01-02	152	Mike	Roger, Ted	DWP	Backoffice, DWP
2023-01-04	163	Jules	Mike, Oizo	DWP	DWP, Tech, DWP

I have 3 slicers based on the date-hiearchy in the table.

Year, Month and Group.

I have a visual with "Agent", "count of TicketID" and i want to measure how many tickets the agents filtered out by the slicers actually handled regardless of which group they handled the ticket in.

So i created a measure

Handled.tickets.agent =

VAR SelectedAgent = SELECTEDVALUE(Data[Agent])

RETURN

CALCULATE(

    SUMX(

        FILTER(

            ALLSELECTED(Data),

            CONTAINSSTRING('Data'[ActiveAgents], SelectedAgent)

        ),

        1

    ),

    ALLSELECTED('Data'[Date]),

   -- Data[Agent] = SelectedAgent //If i add this i don't get the first error with "ghostAgents" but i do get the second error where the numbers are skewed)

)

--

I've tried various filters in above measure and parts of it work but not everything.

If i use the "yearslicer" it shows some agents correct, the "Green agent" did close 470 tickets and was involved in 491 tickets.

But i also get agents who did not close or handle any tickets that year (such as people who quit years ago but they are atleast were part of the group i filter on).

But with the same measure and an additional filter (Year + Month) i get an additional error

The yellow-agent did close 61 tickets in April of 2023, however, when i manually check how many tickets he was involved in the number should be 71. 

Im guessing im messing something up with the filters and putting filter on filter so the values gets skewed but i can't figure out how to solve it.

Any suggestions on how i should do this or what im doing wrong?

[tamerj1]

Hi @NotMoike 
You said the Group is in the slicer. So what do you mean by " regardless of which group they handled the ticket in"? Do you want to ignore the filter of the selected group(s)? 

[NotMoike]

Yes, kindof.

I want the "groupslicer" to just list the agents in that group (and with the additional filter of the date slicers. So if a agent closed a ticket in 2022 but never again, that agent wouldnt be visible if i "slice 2023" in the date. The value for "activeagents" should reflect the value regardless of the group in the slicer, but it needs to accomodate for the date-slice. If that makes sense