Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
andycox
New Member

Getting Count from multiple values against single ID

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

 

 
7 REPLIES 7
andycox
New Member

Here's the data for reference.

 

EXCEL_2020-05-14_19-23-06.png

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"))
 
 
1.jpg
 
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!!
 
 

Thank you @harshnathani  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?

@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.

 

1.jpg

 

 

 

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!!

 

 

@harshnathani  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 NameChatIDUniqueTags 
Agent1gh252
Agent1xf232
Agent2dxf232
 
Basically I want to keep only those results that have Tag1 in it. 
 
image.png

 

@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])))
 
 
1.jpg
 
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

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)

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.