A Bit of Help with my Data Model and Measures Please

[adamlang]

Hi All,

I'm very much a Power BI beginner, but with the help of the kind folk here I've be able to make a lot of progress learning, and developing a dashboard for my organisation, a small charity.

I've created a number of measures, simple and more complex to count various activities and unique people across our CRM. However, I've having trouble getting the filtering to work correctly, when I want to cross filter from another table.

For example, I have two measures, one that count Enrolment Starts (Unique People) and One that Counts enrolment finishes (Unique People).  There's usually open referals at any one time, and so more starts than finishes within a period, say a year. Because the start and finish dates are in the same table I use an inactive relationship to count the finishes. I've lots of similar measures counting various things - appointments, phone calls, workshop attendances etc.

In my visuals I'm stuggleing to be able to filter these measures by catagories in other tables. For example I might want to break the figures down by ethnicity (from by Contacts Table) or By project or service (Both in a Project table - but also sometime present in a merged column).

I think my data model is the problem. I've stuggled to set up all the relationships I would think logitacal because they would introduce ambiguity. So I have many inactive relationships across my model. When I try to use these inactive relationships alongside say a USERELATIONSHIP to my Contacts table, I end up with the same numbers for each ethnicity for both enrolment starts and finshes. This is clearly wong.

The measures look like this:

Enrolments Count of Unique People - Started = CALCULATE ( DISTINCTCOUNTNOBLANK(pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP (pre_projectenrolment[Contact.contactid], Contact[contactid]))

Enrolments Count of Unique People - Finished = CALCULATE( DISTINCTCOUNTNOBLANK(pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP('Calendar'[Date], pre_projectenrolment[pre_enddate]), USERELATIONSHIP (pre_projectenrolment[Contact.contactid], Contact[contactid]))

The table I think is wrong looks like this:

Finally, my date model looks like this (not all relationships are shown here):

I think I might need to re-design the data model with more tables - separating out enrollment starts and finishes into different tables, and maybe introduce some other helper tables? For example, i'm not really sure why the Referals table wouldn't have an active relationship with the Contacts table - I think through the data table there is some ambiguity with the Calendar table. Bit confused by that to be honest.

Where I think it'll get more complicated is I have a number of measures that seek to count unique people across all of the tables, using the User IDs. Is there a easy to get all the relationships to go back to the Contacts table or the Project table?

An example of such a measures is:

Total Unique People Supported (All Activities) = // Test Measure to Count Distict people that have had anyone of the following engagements with us, also using Legacy Entities to support time series graphs

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[ccl3030_uniquecrmnumber]))

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 _AllPeople = CALCULATETABLE(DISTINCT(UNION(_EnrollmentStarts,_EnrollmentEnds,_NewPeopleRegistered,_WHUniquePeople,_ReferalsUniquePeople,_OutcomesUniquePeople,_AppointmentsUniquePeople,_WSAUniquePeople,_QualificationsUniquePeople,_PhoneCalls,_Tasks)))

RETURN

    COUNTROWS(DISTINCT(_AllPeople))

I'd like to be able join all that data to the other tables for filtering.

Happy to take any advice.

Many thanks,

Adam

[adamlang]

I think I need a new table to seperate off the date elements (registation, modified on, last activity date, etc) of my Contacts table - its blurring the difference between fact and dimension, right?