Forum Discussion

Eparfitt1002's avatar
Eparfitt1002
Regular Visitor
3 years ago
Solved

DAX Count Unique Id when another column contains two values in different rows.

Hello !    I have a dataset with one column that contains a unique ID that indicates a phone call and another calumn that has tags of subjects detected during those calls. If a single call has 4 ta...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi Eparfitt1002 
    Could you please attach a table with an example of data and the desired results so that we can better understand your request?

  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Eparfitt1002 
    In order to achieve the desired outcome.
    The first step is to add a calculated column that concatenates the texts by ID to the "height"
    Dax Funtion:

    Concatenated Text = CONCATENATEX(FILTER('Table', 'Table'[UniqueID] = EARLIER('Table'[UniqueID])), 'Table'[Tags], ", ")

     


     

    A second step involves counting based on whether the combination you are seeking is present.
    DAX formula for a measure

    Counter = CALCULATE(DISTINCTCOUNT('Table'[UniqueID]),CONTAINSSTRING('Table'[Concatenated Text],"Escalated Client, Agent Expressed Empathy"))

     

    **If string order can also be reversed, you can add another comparison
    with OR condition

    Link to sample File 

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



Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
3 years ago

Hi Eparfitt1002 
Could you please attach a table with an example of data and the desired results so that we can better understand your request?

Eparfitt1002's avatar
Eparfitt1002
Regular Visitor
3 years ago

Certainly! 

 

In the below example, I would want this formula to detect when Tags "Agent Expressed Empathy" and "Escalated Client" were both on the same call, and count the number of times that happens. 

 

The below table would be a count of 1 when this happened on Call 4. 

UniqueIDTags
Call 1Store Issue
Call 1Escalated Client
Call 2Billing Issue
Call 3Website Issue
Call 4Escalated Client
Call 4Agent Expressed Empathy
Call 5Billing Issue
  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    Do you only check that combination?

    • Eparfitt1002's avatar
      Eparfitt1002
      Regular Visitor
      3 years ago

      Right. I'm attempting to create a percentage that calculates how often my agents are using empathy during escalated calls. 

       

      So if it has the same UniqueID and those two values, it would count that call. Then I'll divide whatever that count is by the count of all the escalation calls to express it as a percentage. 

       

      Thanks!

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        3 years ago

        Eparfitt1002 
        In order to achieve the desired outcome.
        The first step is to add a calculated column that concatenates the texts by ID to the "height"
        Dax Funtion:

        Concatenated Text = CONCATENATEX(FILTER('Table', 'Table'[UniqueID] = EARLIER('Table'[UniqueID])), 'Table'[Tags], ", ")

         


         

        A second step involves counting based on whether the combination you are seeking is present.
        DAX formula for a measure

        Counter = CALCULATE(DISTINCTCOUNT('Table'[UniqueID]),CONTAINSSTRING('Table'[Concatenated Text],"Escalated Client, Agent Expressed Empathy"))

         

        **If string order can also be reversed, you can add another comparison
        with OR condition

        Link to sample File 

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