Forum Discussion

andycox's avatar
andycox
New Member
6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!!
       
       
      • andycox's avatar
        andycox
        New Member
        6 years ago

        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?