Forum Discussion
Identifying user tickets
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,
Thank you for your reply.
However, it's returning this error:
11 Replies
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,
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.
Hello!
VAR TABLE =TOPN(1,SUMMARIZE(zendesk,zendesk[Requester],zendesk[Category],"TotalRequirements", COUNTROWS(zendesk)),[TotalRequirements],
DESC)RETURNMAXX(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"Thank you for your reply.
However, it's returning this error:
Hello!
Looks like its missing the name of the measure,
"VAR TABLE =" is a Dax Function
It cant recognize "RETURN" because cant recognize "VAR"
Hi,
Share some data to work with and show the expected result clearly.
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,
Hi,
Based on the data that you have shared, show the expected result clearly.
