topic Re: Getting Count from multiple values against single ID in DAX Commands and Tips

Getting Count from multiple values against single ID

andycox — Thu, 14 May 2020 09:11:57 GMT

Each unique ID can have multiple(tags) rows in my data set.

I want a measure to countdistinct unique IDs which contain a specific Tag.

https://drive.google.com/open?id=10hKDACMttzQKfZI1lo8IFsuHY3JWVHsK

Powerbi file is attached here.

What I want to calculate in a metric :

1. Total Chats by each Agent which contain Tag1.

2. Total count of unique tags by each agent for chats which contain Tag1.

Kindly help

Re: Getting Count from multiple values against single ID

andycox — Thu, 14 May 2020 09:23:53 GMT

Here's the data for reference.

Re: Getting Count from multiple values against single ID

Anonymous — Thu, 14 May 2020 10:16:46 GMT

Hi andycox ,

Please use below measures.

Contains Tag1 = CALCULATE(COUNT(Sheet1[Chat ID]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Agent Name ]),Sheet1[Tag] = "Tag1"))

DistinctChatID = CALCULATE(DISTINCTCOUNT(Sheet1[Chat ID]), FILTER(ALLEXCEPT(Sheet1,Sheet1[Agent Name ]),Sheet1[Tag] = "Tag1"))

Incase you need the unique tags fo each agent

Unique Tags = CALCULATE(DISTINCTCOUNT(Sheet1[Tag]),ALLEXCEPT(Sheet1,Sheet1[Agent Name ]))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Getting Count from multiple values against single ID

andycox — Thu, 14 May 2020 14:14:01 GMT

Thank you Anonymous for quick reply.

Here's what I am struggling with still.

Average of unique tags by each agent for all chats which contain Tag1.

E.g. If an Agent has 150 chats. 100 of them have Tag1 and other tags. 50 of them do not have Tag1.

I need to calculate the average of unique tags used for each of those 100 chats.

Also , in another calculation, list down all unique chat IDs ( with Tag1) and see how many tags does each conversation have.

Is that something you can help with?

Re: Getting Count from multiple values against single ID

Anonymous — Thu, 14 May 2020 15:51:26 GMT

andycox ,

The question is totally confusing.

No idea on what does this mean 'unique tags by each agent for all chats which contain Tag1' .

You have a column for Tags where you have values like Tag1 , Tag3, etc. What does unique tag which contains Tag1 mean. Do you want the unique chat id's which contain Tag1 ?

For the 2nd question, is this what you need.

DistinctChatIDC = CONCATENATEX( FILTER(Sheet1, Sheet1[Tag] = "Tag1"),Sheet1[Chat ID],",")

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Getting Count from multiple values against single ID

andycox — Fri, 15 May 2020 05:18:24 GMT

Anonymous Thank you for the response, very helpful.

I have used your UniqueTags in this way:

UniqueTags = CALCULATE(DISTINCTCOUNT(Sheet1[Tag]),ALLEXCEPT(Sheet1,Sheet1[Agent Name ],Sheet1[Chat ID]))

It removes the visual filter from AgentName and Chat ID to give me UniqueTags against Each ChatID.

The Chat ID rows highlighted in Red have "Tag1" in it.

Measure I am after will return this table

Agent Name	ChatID	UniqueTags
Agent1	gh25	2
Agent1	xf23	2
Agent2	dxf23	2

Basically I want to keep only those results that have Tag1 in it.

Re: Getting Count from multiple values against single ID

Anonymous — Fri, 15 May 2020 06:27:23 GMT

andycox ,

Please use the below measure

Unique Tags = CALCULATE(Count(Sheet1[Tag]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Agent Name ],Sheet1[Chat ID]),"Tag1" IN VALUES(Sheet1[Tag])))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Getting Count from multiple values against single ID

Anonymous — Mon, 17 Aug 2020 12:16:25 GMT

Hi andycox ,

Did the solution work for you.

Please mark as solution for other community members to benefit.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)