Distinct Count with multiple inactive relationships

[adamlang]

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

            )

        )

)

[v-rongtiep-msft]

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.

[adamlang]

@v-rongtiep-msft 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]

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]

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

[adamlang]

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

            )

      )

)