Clients in multiple categories

[eburke]

Hi all,

I have a problem that I'm not sure how to even start with so hoping someone out there can give me a hand.  I have a table that listes all our customers and the number of times they have received task they have received.  These tasks are linked to another table called "Service Types" that categories all the tasks into a list of 20 different service types.  I have a visual which shows the customer name and shows the distinct count of whether or not they have received a task under each service type:

What I'm trying to achieve is instead of the distinct count in the total (showing 1 client) is a count of how many service types they have used and then filter the table to only show clients who have accesses more than one service.

At the moment I'm going round in circles trying to work out how to achieve this.  Any help would be appreciated.  Thanks, Emma

[Ashish_Mathur]

Hi @eburke,

Share the link from where i can download your file.

[eburke]

Hi, Sorry, our organisation has the files locked down and they can't be shared, too much private client information.

[Ashish_Mathur]

Hi @eburke,

I am not interested in your actual data.  Share a dummy dataset.  WIth respect to that dummy dataset, show your expected result.

[eburke]

Thank you,

I'll try to do this over the next few days, unfortunately I don't have time to create a dummy dataset at the moment.  Thanks, Emma.

[eburke]

Ok i have some dummy data but don't know how to link the file so other people can see it.

[v-ljerr-msft]

Hi @eburke,

You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[eburke]

Nope sorry, can't even do it through one drive, it's all locked down.

[eburke]

I'll see if i can explain it better with a snapshot of the test data.

In the top table I am using distinct count of client names by service type, this gives me a value of 1 for each service they have accessed regardless of how many times they have received that service type.  This is what I want, however in the total it of course also only gives the value of 1 distinct customer regardless of how many services they have accessed.

In the bottom table I use a straight count of client names by service type and get the number of times they have used each service and the totals.  What I actually want is the top table showing 1 for each service type they have used, but the Total to show the total number of service types they have used, for example Customer B total would be 3.

Hope that makes sense.

[v-ljerr-msft]

Hi @eburke,

---

What I'm trying to achieve is instead of the distinct count in the total (showing 1 client) is a count of how many service types they have used and then filter the table to only show clients who have accesses more than one service.

---

If I understand you correctly, you should be able to follow steps below to get your expected result.

1. Use the formula blow to create a new measure.

Measure = COUNTROWS(Table1)

2. Show the measure as Values instead on your matrix visual.

3. Apply a visual level filter(Measure is greater than 1) to filter the customers on the matrix.

In addition, here is the sample pbix file for your reference. 

Regards

[eburke]

Hi, thanks for your answer, unfortunately it's not exactly what I'm after. 

Sorry probably didn't explain it well enough.  In the data that the results are drawn from they can have multiple tasks from the same service type, so if I try to do anything that counts the rows etc. Then the table for service types can have a number higher than 1 for one service.  i.e. 3 tasks under the category Transport will give a total for the customer of 3, even if they have only had that one service type.  If I use a distinct count then I get the desired 1 count in the service type regardless of how many tasks they've had in that service type, but then the total also remains as 1 which doesn't help me sort by customers who have had multiple service types.  Sorry really hard to explain in writing.  What i'm trying to see is something like this:

Customer A has 10 tasks (rows in the data) relationship table categorises these tasks into 2 services, I need a table that shows:

Customer A

Service Type 1 = 1

Service Type 2 = 1

Total = 2

Then i can filter result by Greater than 1.  At the moment if I just try to count rows then I get a result that shows:

Customer A

Service Type 1 = 3

Service Type 2 = 7

Total = 10

If I try using distinct count then I get:

Customer A

Service Type 1 = 1

Service Type 2 = 1

Total = 1

Hopefully that makes more sense.  Thanks,