best practices to count entries per tags

[martinga]

We have installed a support ticket solution, and for faster reporting I would like make a new report. One of the nessasary visuals i need is the count per ticket and tag. The problem I have is that, some entries have only one tag, but many tickets have two and more tickets. 

What is here the best way to make the visual to count the tickets per tag.

Thanks a lot for your help. 

Possible Tags

Location
Order
Support Customer
Diverse
Support intern
Waiting for customer

#	Title			State	Priority	Tags
57866	Ticket Description	closed	2 normal	Location,Order,Support Customer
57865	Ticket Description	closed	2 normal	Location
57863	Ticket Description	closed	2 normal	Diverse
57861	Ticket Description	closed	2 normal	Order,Location
57860	Ticket Description	closed	2 normal	Order
57859	Ticket Description	closed	2 normal	Order
57858	Ticket Description	closed	2 normal	Support Customer
57857	Ticket Description	closed	2 normal	Support Customer
57856	Ticket Description	closed	2 normal	Support Customer
57854	Ticket Description	closed	2 normal	Support internal
57852	Ticket Description	closed	2 normal	Order,Support Customer
57851	Ticket Description	open	2 normal	Diverse,Support intern,Sales,Waiting for Customer
57850	Ticket Description	closed	2 normal	Order

, 

[v-yuta-msft · Accepted solution]

@martinga ,

You can create a column in Tag table using dax below:

Count = 
VAR Current_Tag = Tag[Tag]
RETURN
CALCULATE(COUNT('Table'[#]), FILTER('Table', FIND(Current_Tag, 'Table'[Tag], , -1) <> -1))

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martinga]

Hello v-yutt-msft

Thanks for your solution. It works perfekt. 

I love Data

[MattAllington]

 There are many ways to solve every problem. The way I would approach this is as follows 

 Create a header table with one row per ticket. 

 Create a Childs table that contains all the tags that have been unpivoted into one row per tag, that also contains the ticket number 

 Join the header table to the child table with a one to many relationship 

With this in place you can do a simple count rows of the child table to get account of the tags

 If necessary you can turn on bidirectional cross filtering so that you can filter on the tags