topic Distinct Count with multiple inactive relationships in DAX Commands and Tips

Distinct Count with multiple inactive relationships

adamlang — Tue, 12 Sep 2023 21:54:46 GMT

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 relationship for each entity to correctly link to my calendar table.

I've tried this, but it's not working:

Unique People Across All Areas = COUNTROWS (

DISTINCT (

UNION (

VALUES ( Appointment[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(Appointment[Appointment Start Date or Scheduled Start Date],'Calendar'[Date])

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] ), USERELATIONSHIP(pre_projectenrolment[pre_startdate])

VALUES ( pre_referral[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(pre_referral[pre_referraldate])

VALUES ( pre_workshopattendance[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(pre_workshopattendance[pre_workshopdate])

VALUES ( new_clientsurveyses[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(new_clientsurveyses[bbbc_datefirstonsquestionairecompleted])

VALUES ( new_clientsurveyses[Contact.ccl3030_uniquecrmnumber] ), USERELATIONSHIP(new_clientsurveyses[bbbc_dateonsfollowupcompleted])

)

Re: Distinct Count with multiple inactive relationships

adamlang — Tue, 12 Sep 2023 21:33:59 GMT

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

)

Re: Distinct Count with multiple inactive relationships

Anonymous — Thu, 14 Sep 2023 02:32:51 GMT

Hi adamlang ,

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

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Distinct Count with multiple inactive relationships

adamlang — Thu, 14 Sep 2023 05:41:38 GMT

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

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.

Total Unique People Supported 2 =

COUNTROWS (

DISTINCT (

UNION (

CALCULATE (

COUNTROWS ( DISTINCT ( VALUES ( Contact[ccl3030_uniquecrmnumber] ) ) ),

USERELATIONSHIP (Contact[createdon], 'Calendar'[Date] )

CALCULATE (

COUNTROWS (

DISTINCT ( VALUES ( pre_projectenrolment[Contact.ccl3030_uniquecrmnumber]) )

USERELATIONSHIP (pre_projectenrolment[pre_enddate], 'Calendar'[Date])

)

Returns "The UNION function expects a table expression for argument '2', but a string or numeric expression was used".

Thanks again,

Adam

Re: Distinct Count with multiple inactive relationships

adamlang — Sat, 16 Sep 2023 12:32:42 GMT

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:

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

Thanks,

Adam

Re: Distinct Count with multiple inactive relationships

adamlang — Mon, 18 Sep 2023 21:11:54 GMT

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:

Total Unique People Supported 2 = // Test Measure to Count Distict people that have had any of the following engagements with us

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

COUNTROWS(DISTINCT(_AllPeople))

Thanks,

Adam