How to Build Measure to extract distinct rows from a table based on multiple possible criteria

[PantherusNZ]

I have a table of support ticket items displaying tickets assigned to various support teams, as well as line items if they've been transferred to or from teams - so there are duplicated line items throughout the data.  I need to report on how many tickets were managed by a number of teams through the time period.

So I want to build a series of measures to pull the numbers of tickets that are either assigned to Team X, were transferred to Team X, or were transferred FROM Team X - and it needs to avoid duplicates - so if a ticket is assigned to that team, was transferred to them, and was transferred from them, it would only return 1 for this filter.

I'm struggling to write the measure - previously I've done CALCULATE(COUNTROWS([Table]),FILTER([Column]="criteria")) but I've not done multiple criteria of distincts.

[v-nuoc-msft · Accepted solution]

Hi @PantherusNZ 

Thank you very much Ritaf1983  for your prompt reply, please allow me to share some content here.

Here's some dummy data

“Table”

Create a measure.

Total_Tickets_Managed_By_Team = 
VAR _SelectTea = SELECTEDVALUE('Table'[AssignedTeam])
RETURN
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[AssignedTeam] = _SelectTea ||
        'Table'[TransferredToTeam] = _SelectTea ||
        'Table'[TransferredFromTeam] = _SelectTea
    )
)

Here is the result.

Regards,

Nono Chen

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

[Ritaf1983]

Hi @PantherusNZ 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly