Forum Discussion
adamlang
Helper III
Helper IIIDistinct Count with multiple inactive relationships
Hi There, I wonder if anyone can help me. I'm trying to count the number of unique user ID that fit a certain date range across a number of different entities. I need to use a particular relation...
adamlangHelper III
Helper IIIJust to add to this.
A number of columns can just use the active relationship in the data model, and so the following measure works for them.
Total Unique People Supported = COUNTROWS(DISTINCT(UNION(VALUES(new_connectionzonevisits[Contact.ccl3030_uniquecrmnumber]),VALUES(pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]), VALUES(pre_workshopattendance[Contact.ccl3030_uniquecrmnumber]), VALUES(pre_referral[Contact.ccl3030_uniquecrmnumber]), VALUES(Appointment[Contact.ccl3030_uniquecrmnumber]))))
I need the count of distinct reference numbers to cover the following columns which all require an inactive relationship:
( Contact[ccl3030_uniquecrmnumber] ), USERELATIONSHIP(Contact[createdon],'Calendar'[Date])
( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(pre_projectenrolment[pre_enddate])
and further the (new_clientsurvery) entity is not properly incorporated into the data model yet, so let's ignore it for now.
That leave the following measure that I'd like to get working:
Total Unique People Supported = COUNTROWS (
DISTINCT (
UNION (
VALUES ( Appointment[Contact.ccl3030_uniquecrmnumber] ),
VALUES ( Contact[ccl3030_uniquecrmnumber] ), USERELATIONSHIP(Contact[createdon],'Calendar'[Date])
VALUES ( new_connectionzonevisits[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP(new_connectionzonevisits[new_timearived],'Calendar'[Date])
VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP(pre_projectenrolment[pre_enddate])
VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]),
VALUES ( pre_referral[Contact.ccl3030_uniquecrmnumber]),
VALUES ( pre_workshopattendance[Contact.ccl3030_uniquecrmnumber])
)
)
)