Unique Person Count Overlapping Venn Diagram across services

[adamlang]

Hi there,

At the early stage of thinking this through, so maybe I don't need a full solution but being pointed in the right direction.

One of my organisations top KPIs is the count of unique people that have had an interaction, over say a year or quarter. These different interactions all link to a Service, a Contacts table, and the Calendar, in the data model.

I count the number of Unique People in measure (copied below) that uses VARs to basically gather up all the unique Ids across about 7 tables of different kinds of activities, and runs the DISTINCT function on them, outputting a single number. This measure is filterable by the services, and we also produce KPIs highlighting how many Unique people each service has worked with.

They'll be a fair amount of overlap between the services, in terms of who they have supported, although we'd like to understand this better and be able to report on it . I'd like to produce a table, or probably a visualusation (using a VENNE diagram) that will highlight the volume of unique people that overlap each of our six services. I.e. distingish between people that only interact with one service, those that might overlap two services, more than two services, and those that have interacted with all the services in the period. Ideal Venn diagram territory.

I'd tried downloading the MAC Software Custom Visualisation Venne Diagram, adding my measure and the services field as a category (not really expecting it to work). It doesn't work, but all the error message says is "Please provide data in a valid format".

The measure works in a simple table with the services as a category, and the total providing a Unique figure for the period - i.e. removing the duplicates. No clue as to why the Venn diagramme wouldn't work, but I'm guessing my measure isn't quite right for this use case, as all the duplicates are removed in the final result and the Venn Diagram will need them to form the overlap.

The measure is:

Total People Supported (Service Activities) =

    VAR _EnrollmentStarts = CALCULATETABLE(DISTINCT(pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]))

    VAR _EnrollmentEnds = CALCULATETABLE(DISTINCT(pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP('Calendar'[Date], pre_projectenrolment[pre_enddate]))

    VAR _NewPeopleRegistered = CALCULATETABLE(DISTINCT('Contact Fact'[ccl3030_uniquecrmnumber]), USERELATIONSHIP('Calendar'[Date], 'Contact Fact'[createdon]))

    VAR _WHUniquePeople = CALCULATETABLE(DISTINCT(new_connectionzonevisits[Contact.ccl3030_uniquecrmnumber]))

    VAR _ReferalsUniquePeople = CALCULATETABLE(DISTINCT(pre_referral[Contact.ccl3030_uniquecrmnumber]))

    VAR _OutcomesUniquePeople = CALCULATETABLE(DISTINCT(new_clientsurveyses[Contact.ccl3030_uniquecrmnumber]))

    VAR _AppointmentsUniquePeople = CALCULATETABLE(DISTINCT(Appointment[Contact.ccl3030_uniquecrmnumber]))

    VAR _WSAUniquePeople = CALCULATETABLE(DISTINCT(pre_workshopattendance[Contact.ccl3030_uniquecrmnumber]))

    VAR _QualificationsUniquePeople = CALCULATETABLE(DISTINCT(new_qualifications[Contact.ccl3030_uniquecrmnumber]))

    VAR _PhoneCalls = CALCULATETABLE(DISTINCT(PhoneCall[Contact.ccl3030_uniquecrmnumber])) // Phone Calls often don't have a CRM Number

    VAR _Tasks = CALCULATETABLE(DISTINCT(Task[Contact.ccl3030_uniquecrmnumber]))

    VAR _AllPeopleActivities = CALCULATETABLE(DISTINCT(UNION(_EnrollmentStarts,_EnrollmentEnds,_ReferalsUniquePeople,_OutcomesUniquePeople,_AppointmentsUniquePeople,_WSAUniquePeople,_WHUniquePeople)))

RETURN

    COUNTROWS(DISTINCT(_AllPeopleActivities))

Not really sure where to start sorting this out - can anyone point me in the right direction? Maybe provide a general tutorial. I'm slowly getting to the point where I'd like to start working out things a bit more for my self rather than just get the answer. But a steer in the right direction would be really helpful.

Not sure If I just need to tweak the measure a little bit, or do it completely differently - maybe I need  to contruct a virual table - which I would need help with.

Many thanks,

Adam 

[adamlang]

@lbendlin Thanks for engaging, and pointing me in the right diection to answer this, its appreciated!

I've made up a demo date model in excel at the link below:

https://www.dropbox.com/scl/fi/ah8j0bjbz5qeiefred1cm/Demo-Data-Model.xlsx?rlkey=981dsh5rsogo11v1zwv0... 

In the first worksheet, it includes two activity tables - Appointments, and Group Session. The dimention tables are a Contact table, and Project/Service table, and the Calendar table. Do let me know if this doesn't work.

In the second worksheet, its includes the format of the table I'd like to output, and the result from the test data - I think we can park the diagram and focus on the table as a starting point.

Basically, using the two activities tables I want to how many people have interacted with each combination of the five services (31 combinations in total). The result being filterable in Power BI by date, and demographic information from the contact table.

Hope that's clear, please do ask any questions if not.

Many thanks,

Adam

[lbendlin]

You will often find that a graphical solution is quicker

is this what you are after?

[adamlang]

Hi @lbendlin,

Thanks. No, I'm not wanting to count the interactions, under each service. It's the unique (distinct really) people that have interacted with each service, across both activities tables, I want to count. Then I need to be able to analyse the overlap between services, I.e. the count of people against the different combinations of services. Mathematically for 5 services they'll be 31 combinations - many of which are null values in the sample data, but won't be in the real data.

Thanks again,

Adam

[adamlang]

So from the demo data, the result would look something like this:

Count of Unique Clients that have interacted with the following  31 combinations of services:
Only One Service	Unique Person Count
Learning	0
Employment	1
Health	2
Advice	0
Social Care	3
Only Two Services
Learning, Employment	1
Learning, Health	0
Learning, Advice	0
Learning, Social Care	0
Employment, Health	0
Employment, Advice	0
Employment, Social Care	1
Health, Advice	0
Health, Social Care	0
Advice, Social Care	0
Only three services
Learning, Employment, Health	0
Learning, Employment, Advice	0
Learning, Employment, Social Care	0
Learning, Health, Advice	0
Learning, Health, Social Care	1
Learning, Advice, Social Care	1
Employment, Health, Advice	0
Employment, Health, Social Care	1
Employment, Advice, Social Care	1
Health, Advice, Social Care	0
Only Four Services
Learning, Employment, Health, Advice	0
Learning, Employment, Health, Social Care	0
Learning, Emplyment, Advice, Social Care	0
Learning, Health, Advice, Social Care	0
Employment, Health, Advice Socail Care	0
All Five Services
Learning, Employment, Health, Advice and Social Care	0

One consideration is that, for example "Employment, Advice", is the same combination as "Advice, Employment", otherwise there would be many more conbinations.

Eventually we might want to analise which services people are coming to first, but that might be an easy tweak, based on the dates of the first activity, once this question is anwsered.

Thanks,

Adam  

[lbendlin · Accepted solution]

Unique Person Count = 
var c = [Combination]
var a = EVALUATEANDLOG(SUMMARIZE('Table',[URN],"Combination",var b = distinct('Table'[Service]) return CONCATENATEX(b,[Service],",",[Service],ASC)))
return countrows(filter(a,[Combination]=c))

check Page2 of the attached.

[adamlang]

Thanks very much @lbendlin 

That is the right answer, my manual count was wrong.

Just working through the detail of how to bring this solution into my main data model. For example wondering how the "Combinations" table itself was built. I looked at the query and could work out how the table developed, but the starting list of services - was that just imported as text, so when I build it i'll use the dimention table, and probably Summerize to start with a full list of the services?

Sorry for such a basic question.

Thanks,

Adam

[lbendlin]

The starting list is static in my example but can instead be derived from the raw data via List.Distinct([Column]). Let me know if you want to see an example.

Oh, and please ignore the EVALUATEANDLOG part, that's a leftover from debugging.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...