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

[Eparfitt1002]

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 tags, then that means the same unique I'd would be in all 4 rows and the tags would be in the tags column in different rows.

How can I write a DAX metric to count the number of times two tags appear together in the same unique ID? 

I want to use that number to include in a percentage calculation. Obviously a calculate / keepfilter option won't work because the "tags" are all in the same column.

Any help would be appreciated!

[Ritaf1983 · Accepted solution]

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 · Accepted solution]

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]

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. 

UniqueID	Tags
Call 1	Store Issue
Call 1	Escalated Client
Call 2	Billing Issue
Call 3	Website Issue
Call 4	Escalated Client
Call 4	Agent Expressed Empathy
Call 5	Billing Issue

[Ritaf1983]

Do you only check that combination?

[Eparfitt1002]

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 · Accepted solution]

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