Identifying user tickets

[adumith]

Hello everyone, 

I have a table called "zendesk" which contains user tickets. Each one has a category assigned to it. I need to identify the requesters with the most tickets per category.

I have tried several formulas, but they all give me the same error, and I don't know how to solve it.

Formula:

TopRequestersPerCategory = 
VAR RequestersPerCategory =
    SUMMARIZE(
        zendesk,
        zendesk[Requester ],
        zendesk[Category],
        "TotalRequirements", COUNTROWS(zendesk)
    )
RETURN
    ADDCOLUMNS(
        SUMMARIZE(
            RequestersPerCategory,
            RequestersPerCategory[Requester],
            "MaxRequirements", MAX(RequestersPerCategory[TotalRequirements])
        ),
        "Category", SELECTEDVALUE(RequestersPerCategory[Category])
    )

Error:

Any ideas?

Thank you,

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result clearly.

[adumith]

There you go.

Ticket_ID	Requester	Category
1	Osbourne Riddle	Ventosanzap
2	Leda Lowey	Rank
3	Bevvy Drinkall	Overhold
4	Ignace Ditts	Duobam
5	Benedikta Halfacre	Duobam
6	Kathlin Gauvain	Konklux
7	Lacie Kamienski	Zontrax
8	Charline McGilleghole	Flexidy
9	Andres Paolone	Y-Solowarm
10	Filberte Robker	Tampflex
11	Emmalynne Rankmore	It
12	Lauryn Tavner	Alpha
13	Dion Blazewski	Subin
14	Waneta Weavill	Duobam
15	Rozanne Maffia	Y-Solowarm
16	Ciro Bruckental	Temp
17	Shannah Jobern	Domainer
18	Elena Rabson	Stronghold
19	Allx Blyth	**bleep**ip
20	Bernardine Symcock	Sonsing
21	Mayne Keir	Otcom
22	Abel Saddler	Biodex
23	Nicky Matijasevic	Keylex
24	Marietta Sjostrom	Bytecard
25	Drake Devers	Ventosanzap
26	Weider Ruoff	Keylex
27	Silva Raincin	Sonair
28	Malorie Pinwill	Veribet
29	Sybyl Carey	Opela
30	Nappy Silbersak	Vagram
31	Layne Shulem	Zamit
32	Alie Lambell	Zathin
33	Graehme Dowding	Stronghold
34	Wendeline Laffling	Alpha
35	Frants Paula	Flexidy
36	Giffer Garbar	Zoolab
37	Rowland Frampton	Alpha
38	Marty Recke	Y-find
39	Heda Pebworth	Zathin
40	Rhona Binyon	Lotlux
41	Jamey Daniaud	Tampflex
42	Perle Milmo	Flexidy
43	Justinn Tsar	Daltfresh
44	Maisey Richen	Zaam-Dox
45	Silvain Mayes	Namfix
46	Jaquith Milella	Latlux
47	Benedetto Arangy	Tres-Zap
48	Kessiah Spurman	Rank
49	Olympia Niezen	It
50	Paula Tomankowski	Rank
51	Wilek Haymes	Veribet
52	Johnathon Janauschek	Lotstring
53	Artair Seebert	Veribet
54	Josi Enrique	Daltfresh
55	Nevins Tern	Y-find
56	Ami Blanpein	Aerified
57	Philip Pritchitt	**bleep**ip
58	Ilyse Wantling	Hatity
59	Fey Salzberger	Sonsing
60	Fredek Lydiatt	Bigtax
61	Jena Drayn	Bigtax
62	Chuck Millett	Gembucket
63	Trumann Willoughey	Quo Lux
64	Tamma Godilington	Trippledex
65	Virginie Houselee	Sub-Ex
66	Alexandr Tilford	Bitwolf
67	Harri Gilks	Span
68	Layney Minet	Y-Solowarm
69	Cosmo Tregensoe	Viva
70	Adey Agron	Daltfresh
71	Lindsay Leed	Tempsoft
72	Shaine Daniau	Transcof
73	Isabella Queyos	Pannier
74	Charlot Wheelwright	Prodder
75	Brana Lamey	Opela
76	Ambros Frazier	Tin
77	Jillie McKearnen	Cardify
78	Dewie Borham	Overhold
79	Clarisse Remmer	Bigtax
80	Lilian Latek	Namfix
81	Ethelin Hinchon	Overhold
82	Sloan Buckles	Biodex
83	Waylon Dyka	Zontrax
84	Genevieve Calam	Voltsillam
85	Adaline Wiburn	Bytecard
86	Trix Urlich	Konklab
87	Nickie Rundall	Y-find
88	Les Babin	Hatity
89	Sybille Stouther	Tin
90	Stewart McSperrin	Namfix
91	Fania Huntress	Matsoft
92	Uriel Liptrod	Rank
93	Tedmund Oubridge	Bytecard
94	Sargent Tiffany	Voyatouch
95	Audrye Runnett	Trippledex
96	Valene O'Duilleain	Zathin
97	Hilde Matussevich	Zoolab
98	Parrnell Hardy-Piggin	Treeflex
99	Corinna Maskell	Wrapsafe
100	Gilly Reek	Solarbreeze
101	Xenos Hillock	Overhold
102	Iorgo Benois	Konklab
103	Malanie Probin	Matsoft
104	Charlena Scneider	Greenlam
105	Brenden Balcers	Span
106	Marcelle Jaye	Bamity
107	Raddy Gonoude	Transcof
108	Alasdair Hitschke	Stronghold
109	Andras Faughnan	Voyatouch
110	Linzy Ambrois	Solarbreeze
111	Judi Chrippes	Alpha
112	Lianna Alecock	Kanlam
113	Sal Casillas	Kanlam
114	Rosaleen Lumm	Namfix
115	Drusy Eastlake	Konklux
116	Katrine Manketell	Tempsoft
117	Charline Massei	Tampflex
118	Ernest McCaughey	Job
119	Ivy Dowson	Voyatouch
120	Daphne Niessen	Transcof
121	Athene Guihen	Namfix
122	Quill Naris	Pannier
123	Leena Jeaffreson	**bleep**ip
124	Ethyl Farmloe	Holdlamis
125	Jourdan Tremayle	Zamit
126	Morse Hullot	Subin
127	Clotilda Littlemore	Solarbreeze
128	Pauline Wallbank	Voltsillam
129	Evin Springtorp	Pannier
130	Petra Muino	Daltfresh
131	Haskell Artrick	Hatity
132	Dyana Haggerston	**bleep**ip
133	Ulla Liggins	Opela
134	Ines MacCallum	It
135	Chelsea Feilden	Sonair
136	Meara Gorioli	Y-find
137	Julian Goodhew	Konklab
138	Corinna Tinwell	Zamit
139	Iggy Hise	Stronghold
140	Gilda Hardaker	It
141	Sybille Bows	Vagram
142	Worden Aberdalgy	Y-find
143	Ardine Grice	Otcom
144	Daisy Dunbleton	Gembucket
145	Mychal Acres	Tin
146	Kippar Berryman	Flexidy
147	Dermot Casarili	Stringtough
148	Hildagarde Sandhill	Konklux
149	Dannye Winks	Sonair
150	Merwin Cowterd	Regrant

Thank you so much,

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result clearly.

[LuizKoller]

Hello!

VAR TABLE =

TOPN(

    1,

    SUMMARIZE(

        zendesk,

        zendesk[Requester],

        zendesk[Category],

        "TotalRequirements", COUNTROWS(zendesk)

        ),

    [TotalRequirements],
    DESC

)

RETURN

MAXX(TABLE, zendesk[Requester])



Only the SUMMARIZE dont give you the results, you need something to return the "TOPN"
Put this Measure in a table with "Category"

[adumith · Accepted solution]

Thank you for your reply.

However, it's returning this error:

[LuizKoller]

Hello!

Looks like its missing the name of the measure,

"VAR TABLE =" is a Dax Function

It cant recognize "RETURN" because cant recognize "VAR"

[adumith]

Hello @LuizKoller ,

What visualization do you reccomend for this?

I'm asking you because, I would like to bring the result into a table visualization, how ever when I try to do it I'm just getting one column with a single column which just show one requester.

I mean I need to shw the result as a table like this:

Thank you in advance, 

[LuizKoller]

Let me get this straight, you want a table with one category per line, the name of the user who opened the most tickets in that category, and the number of tickets, correct?

1. I would make a table putting the category in the rows and the measurement mentioned above, like this:

If you want the user not to appear in total, just make a simple adjustment to the measure:

2. Now for the total, it depends on what total you want.

If you want the total number of tickets regardless of the user, just add a count.

* My BI is in Portuguese, but I think it's understandable.

Now, if you want the number of tickets only for the user who opened the most tickers, then you need a measure.

Measure Example for Count = 
VAR ExampleTable = 
TOPN(
    1,
    SUMMARIZE(
        zendesk,
        zendesk[Requester],
        zendesk[Category],
        "TotalRequirements", COUNTROWS(zendesk)
        ),
    [TotalRequirements],
    DESC
)


RETURN
IF(
    ISINSCOPE(zendesk[Category]),
    MAXX(ExampleTable, [TotalRequirements])
)

(I'm not putting a print of the result because the example data in another comment does not show any user who has the same ticket category more than once)

Hope it helps

[adumith]

This is shameful, a thousand apologies. 🙄

Rookie mistake. 😝

It's working smoothly.

Thank you so much.

[adumith]

Hello everyone, 

I have tried other options but unfornately I don't get it....

TopRequestersPerCategory = 
    SUMMARIZE(
        ADDCOLUMNS(
            zendesk,
            "TotalTickets", 1
        ),
        zendesk[Requester],
        zendesk[Category],
        "TotalTickets", SUM(zendesk[TotalTickets])
    )

TopRequestersPerCategory =
    ADDCOLUMNS(
        SUMMARIZE(
            zendesk,
            zendesk[Requester],
            zendesk[Category],
            "TotalTickets", COUNTROWS(zendesk)
        ),
        "Rank", RANKX(
            FILTER(
                ALL(zendesk),
                zendesk[Requester] = EARLIER(zendesk[Requester])
            ),
            [TotalTickets],
            ,
            DESC
        )
    )

Any ideas?

My goal is be able to determinate a list of the requesters and the categories in which they have the most tickets? In other words, the formula should search for each applicant which categories have the most tickets and how many tickets.

Thank you in advance, 

[adumith]

Ok folks,

I'm still investigating.

I found a tool called DAX Studio, where I can test the formulas and then if it does what I expect then I copy it to Power BI.

Well, it turns out that the formula in the tool works but not in Power BI, so I am definitely doing something wrong.

I have checked the table, the data structure, the column names and everything is fine.

Otherwise the formula would not work in DAX Studio.

DAX Studio

Power BI

Why?

What I'm doing wrong?

Thank you guys and sorry for be anoying.