Forum Discussion

adamlang's avatar
adamlang
Icon for Helper III rankHelper III
3 years ago

Distinct 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...
adamlang's avatar
adamlang
Icon for Helper III rankHelper III
3 years ago

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
adamlang's avatar
adamlang
Icon for Helper III rankHelper III
3 years ago

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
  • adamlang's avatar
    adamlang
    Icon for Helper III rankHelper III
    3 years ago

    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