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=10hKDACMttzQKfZI1lo...
andycox's avatar
andycox
New Member
6 years ago

Here's the data for reference.

 

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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

       

      • andycox's avatar
        andycox
        New Member
        6 years ago

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