Forum Discussion

PantherusNZ's avatar
PantherusNZ
Helper I
2 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.