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

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.