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...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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'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