Distinct Count with multiple inactive relationships

Question

Hi There,&nbsp;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 relationship for each entity to correctly link to my calendar table.&nbsp;I've tried this, but it's not working:&nbsp;Unique People Across All Areas = COUNTROWS (&nbsp; &nbsp; &nbsp; &nbsp; DISTINCT (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNION (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( Appointment[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(Appointment[Appointment Start Date or Scheduled Start Date],'Calendar'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( Contact[ccl3030_uniquecrmnumber] ), USERELATIONSHIP(Contact[createdon],'Calendar'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( new_connectionzonevisits[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(new_connectionzonevisits[new_timearived],'Calendar'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(pre_projectenrolment[pre_enddate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(pre_projectenrolment[pre_startdate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_referral[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(pre_referral[pre_referraldate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_workshopattendance[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(pre_workshopattendance[pre_workshopdate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( new_clientsurveyses[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(new_clientsurveyses[bbbc_datefirstonsquestionairecompleted])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( new_clientsurveyses[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(new_clientsurveyses[bbbc_dateonsfollowupcompleted])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ))

adamlang · Answer

I think i've made some progress with this using VAR, it seems to be working, although I'm not sure how I would manually test it. Does the following make sense:&nbsp;Total Unique People Supported 2 = // Test Measure to Count Distict people that have had any of the following engagements with usVAR _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]), USERELATIONSHIP('Calendar'[Date], Contact[createdon]))VAR _WHUniquePeople = CALCULATETABLE(DISTINCT(new_connectionzonevisits[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 _AllPeople = CALCULATETABLE(DISTINCT(UNION(_EnrollmentStarts,_EnrollmentEnds,_NewPeopleRegistered,_WHUniquePeople,_OutcomesUniquePeople,_AppointmentsUniquePeople,_WSAUniquePeople)))RETURN&nbsp; &nbsp; COUNTROWS(DISTINCT(_AllPeople))&nbsp;Thanks,&nbsp;Adam

adamlang · Answer

Just to add to this.&nbsp;A number of columns can just use the active relationship in the data model, and so the following measure works for them.&nbsp;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]))))&nbsp;I need the count of distinct reference numbers to cover the following columns which all require an inactive relationship:&nbsp;( Contact[ccl3030_uniquecrmnumber] ),&nbsp;USERELATIONSHIP(Contact[createdon],'Calendar'[Date])( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber] ),&nbsp;USERELATIONSHIP(pre_projectenrolment[pre_enddate])&nbsp;and further the (new_clientsurvery) entity is not properly incorporated into the data model yet, so let's ignore it for now.&nbsp;That leave the following measure that I'd like to get working:&nbsp;Total Unique People Supported = COUNTROWS (&nbsp; &nbsp; &nbsp; &nbsp; DISTINCT (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNION (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( Appointment[Contact.ccl3030_uniquecrmnumber] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( Contact[ccl3030_uniquecrmnumber] ), USERELATIONSHIP(Contact[createdon],'Calendar'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( new_connectionzonevisits[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP(new_connectionzonevisits[new_timearived],'Calendar'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP(pre_projectenrolment[pre_enddate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_referral[Contact.ccl3030_uniquecrmnumber]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( pre_workshopattendance[Contact.ccl3030_uniquecrmnumber])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; ))

anonymous · Answer

Hi&nbsp;adamlang&nbsp;,
Maybe this would help you.Please have a try.
Total Unique People Supported =
COUNTROWS (
    DISTINCT (
        UNION (
            CALCULATE (
                COUNTROWS (
                    DISTINCT ( VALUES ( Appointment[Contact.ccl3030_uniquecrmnumber] ) )
                ),
                USERELATIONSHIP ( Appointment[Appointment Start Date or Scheduled Start Date], Calendar[Date] )
            ),
            CALCULATE (
                COUNTROWS ( DISTINCT ( VALUES ( Contact[ccl3030_uniquecrmnumber] ) ) ),
                USERELATIONSHIP ( Contact[createdon], Calendar[Date] )
            ),
            CALCULATE (
                COUNTROWS (
                    DISTINCT (
                        VALUES ( new_connectionzonevisits[Contact.ccl3030_uniquecrmnumber] )
                    )
                ),
                USERELATIONSHIP ( new_connectionzonevisits[new_timearived], Calendar[Date] )
            ),
            CALCULATE (
                COUNTROWS (
                    DISTINCT ( VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber] ) )
                ),
                USERELATIONSHIP ( pre_projectenrolment[pre_enddate], Calendar[Date] )
            ),
            CALCULATE (
                COUNTROWS (
                    DISTINCT ( VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber] ) )
                ),
                USERELATIONSHIP ( pre_projectenrolment[pre_startdate], Calendar[Date] )
            ),
            CALCULATE (
                COUNTROWS (
                    DISTINCT ( VALUES ( pre_referral[Contact.ccl3030_uniquecrmnumber] ) )
                ),
                USERELATIONSHIP ( pre_referral[pre_referraldate], Calendar[Date] )
            ),
            CALCULATE (
                COUNTROWS (
                    DISTINCT ( VALUES ( pre_workshopattendance[Contact.ccl3030_uniquecrmnumber] ) )
                ),
                USERELATIONSHIP ( pre_workshopattendance[pre_workshopdate], Calendar[Date] )
            )
        )
    )
)

&nbsp;
How to Get Your Question Answered Quickly&nbsp;
&nbsp;
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
&nbsp;
Best RegardsCommunity Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;
&nbsp;

adamlang · Answer

Anonymous&nbsp;Thanks for taking the time to respond, I think this in on the right lines, i.e. nesting each USERELATIONSHIP within its own CALULATE function.However I think sometime might be wrong with the syntax maybe, I get the following error:"The UNION function expects a table expression for argument '7', but a string or numeric expression was used."&nbsp;I tried simplifing the DAX code to just look at two entities which I know need to use the inactive relationships, just to check the syntax, and get started, making sure it wasn't a data model issue somewhere. but that returned a similar error message too.&nbsp;Total Unique People Supported 2 =COUNTROWS (&nbsp; &nbsp; DISTINCT (&nbsp; &nbsp; &nbsp; &nbsp; UNION (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS ( DISTINCT ( VALUES ( Contact[ccl3030_uniquecrmnumber] ) ) ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP (Contact[createdon], 'Calendar'[Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DISTINCT ( VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]) )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP (pre_projectenrolment[pre_enddate], 'Calendar'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ) &nbsp; &nbsp;&nbsp; &nbsp; ))&nbsp;Returns "The UNION function expects a table expression for argument '2', but a string or numeric expression was used".&nbsp;Thanks again,&nbsp;Adam

adamlang · Answer

Would CALCULATETABLE maybe offer a solution if UNION is expecting a table rather than a value? I've not used CALCULATETABLE much, and the videos I just watched confused me.

If it helps I'm able to use individual measures to calulate the unique clients in each entity as below, I just want to be able to count unque clients across all the entities together, i.e. removing any duplicant client numbers.

My current indiviudal measures are:

New People Registered = CALCULATE(DISTINCTCOUNTNOBLANK(Contact[ccl3030_uniquecrmnumber]), USERELATIONSHIP('Calendar'[Date], Contact[createdon]))

Appointments Unique People = DISTINCTCOUNTNOBLANK(Appointment[Contact.ccl3030_uniquecrmnumber])

Unique People Referred = DISTINCTCOUNTNOBLANK(pre_referral[Contact.ccl3030_uniquecrmnumber])

WH Unique People = DISTINCTCOUNTNOBLANK(new_connectionzonevisits[Contact.ccl3030_uniquecrmnumber])

Unique People Enrolments Started = DISTINCTCOUNTNOBLANK(pre_projectenrolment[Contact.ccl3030_uniquecrmnumber])

Unique People Enrolment Finished = CALCULATE( DISTINCTCOUNTNOBLANK(pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]), USERELATIONSHIP('Calendar'[Date], pre_projectenrolment[pre_enddate]))

Outcome Unique People = DISTINCTCOUNTNOBLANK(new_clientsurveyses[Contact.ccl3030_uniquecrmnumber])

and in addition I can use a measure that takes all the entities that don't require a specific relationships:

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]), VALUES(new_clientsurveyses[Contact.ccl3030_uniquecrmnumber]))))

How do I add the inactive relationships to the total unique count.

Thanks,

Adam

Forum Discussion

Distinct Count with multiple inactive relationships

5 Replies

Related content

Relationship

Relationships

Relationship Issue

Date Relationships - Multiple Tables, Multiple dates.

distinct count of multiple columns