Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
adamlang
Helper III
Helper III

Unique Person Count Overlapping Venn Diagram across services

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 

1 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))

lbendlin_0-1700577748947.png

check Page2 of the attached.

 

View solution in original post

8 REPLIES 8
adamlang
Helper III
Helper III

@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

You will often find that a graphical solution is quicker

lbendlin_0-1700530408444.png

 

is this what you are after?

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

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 ServiceUnique Person Count
Learning0
Employment1
Health2
Advice0
Social Care3
  
Only Two Services 
  
Learning, Employment1
Learning, Health0
Learning, Advice0
Learning, Social Care0
Employment, Health0
Employment, Advice0
Employment, Social Care1
Health, Advice0
Health, Social Care0
Advice, Social Care0
  
Only three services 
  
Learning, Employment, Health0
Learning, Employment, Advice0
Learning, Employment, Social Care0
Learning, Health, Advice0
Learning, Health, Social Care1
Learning, Advice, Social Care1
Employment, Health, Advice0
Employment, Health, Social Care1
Employment, Advice, Social Care1
Health, Advice, Social Care0
  
Only Four Services 
  
Learning, Employment, Health, Advice0
Learning, Employment, Health, Social Care0
Learning, Emplyment, Advice, Social Care0
Learning, Health, Advice, Social Care0
Employment, Health, Advice Socail Care0
  
All Five Services 
Learning, Employment, Health, Advice and Social Care0

 

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  

 

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))

lbendlin_0-1700577748947.png

check Page2 of the attached.

 

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

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.