In need of a measure outputting text value based on frequency of another text value

[rbreneman]

Hi!

I want to write a measure that will produce the person with the top number of closed tickets for today, excluding any tickets that have a blank owner. I've included an example below of my data structure. Using this example and assuming today's date is 6/1/2021 the measure would output "Frank (2)". I suppose it's possible there could be a tie score at some point in the day, so if we can account for that with something like "TIE - Frank and George (15)" that would be awesome.

I know I need a filter for DateClosed >= TODAY() && Owner <> BLANK() but I'm struggling with how to use DISTINCTCOUNT and other functions to get to my end goal. I've been trying various combinations of TOPN and RANKX and can't quite get it to do what I want.

My data is structed as so...

ID	DateClosed	Owner
1	5/28/2021	Bob
2	5/29/2021	George
3	6/1/2021	George
4	6/1/2021	Frank
5	6/1/2021	Frank

Thanks in advance for any help you can provide!

[AlexisOlson · Accepted solution]

One way to do this:

Top Owner = 
VAR Summary =
    SUMMARIZE (
        FILTER (
            Table1,
            Table1[DateClosed] = TODAY () && NOT ( ISBLANK ( Table1[Owner] ) )
        ),
        Table1[Owner],
        "@Cnt", COUNT ( Table1[ID] )
    )
VAR MaxCnt = MAXX ( Summary, [@Cnt] )
VAR CntText = " (" & FORMAT ( MaxCnt, "0" ) & ")"
VAR TopOwners = FILTER ( Summary, [@Cnt] = MaxCnt )
RETURN
    IF (
        COUNTROWS ( TopOwners ) > 1,
        "TIE - " & CONCATENATEX ( TopOwners, Table1[Owner], " and " ) & CntText,
        MAXX ( TopOwners, Table1[Owner] ) & CntText
    )

[rbreneman]

Thank you, thank you, thank you!!

This is amazing! The measure is working just as I had hoped.