Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
)
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
)
Thank you, thank you, thank you!!
This is amazing! The measure is working just as I had hoped.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |