Userelationship on a calculated column with Filter issue

[Anonymous]

Hi community,

Following is my dataset:

My pbi file:  https://1drv.ms/u/s!Ag919_pO_UKrgQhdlDIIlXqro0QP?e=Jjmbph

Service_attended_date is a calculated column. On a graph I have a measure :

Services attended = CALCULATE(DISTINCTCOUNT('Table'[Client_id]),USERELATIONSHIP('Table'[Service_attended_date],Datedim[Date]),filter('Table','Table'[Case_id] = 'Table'[Service_case_id]))

But this measure results in blank.

Please suggest.

Thanks in advance.

[v-kelly-msft]

Hi @Anonymous ，

Modify your measure as below:

Services attended = CALCULATE(DISTINCTCOUNT('Table'[Client_id]),filter('Table','Table'[Case_id] = 'Table'[Service_case_id]))

And you will see:

I'm not quite sure why you need to use the relationship to get the distinctcount value,as it can be directly calculated out in its table.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft  I have to use userelationship as there is already an active relationship between Datedim & my table on a different column.

[amitchandak]

@Anonymous , this one seems to working

Services attended = CALCULATE(CALCULATE(DISTINCTCOUNT('Table'[Client_id]),FILTER('Table','Table'[Case_id] ='Table'[Service_case_id])),USERELATIONSHIP('Table'[Service_attended_date],Datedim[Date]))

[Anonymous]

@amitchandak  sorry this doesn't work..

[PaulDBrown]

@Anonymous 

Try:

Services attended1 =
VAR CaseID =
    VALUES ( 'Table'[Case_id] ) // This creates a table of unique Case_id Values
VAR ServiceCaseID =
    VALUES ( 'Table'[Service_case_id] ) // This creates a table of unique Service_case_id Values
VAR FiltTable =
    INTERSECT ( CaseID, ServiceCaseID ) //Returns a table with rows where Case_id and Service_case_id are the same
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Client_id] ),
        USERELATIONSHIP ( 'Table'[Service_attended_date], Datedim[Date] ),
        FiltTable
    )

[Anonymous]

@PaulDBrown thanks, this works!! Can I ask if I want to apply some more filters , how can I do that?

for example : Case_id = service_case_id only for those records where

Service_code ="Stage1" and Service_status  in {"Closed","Comlplete"}

[PaulDBrown · Accepted solution]

@Anonymous 

Apologies since I can't check if it works since the sample you provided does not have the fields you want to filter by, but give this a try:

Services attended (more filters) =
VAR CaseID =
    CALCULATETABLE ( 
        VALUES ( 'Table'[Case_id] ), 
         'Table'[Service_code] = "Stage1", 
         'Table'[Service_status] IN { "Closed", "Complete" }
    )
VAR ServiceCaseID =
    CALCULATETABLE (
        VALUES ( 'Table'[Service_case_id] ), 
          'Table'[Service_code] = "Stage1",
          'Table'[Service_status] IN { "Closed", "Complete" }
    )
VAR FiltTable =
    INTERSECT ( CaseID, ServiceCaseID )

RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Client_id] ),
        USERELATIONSHIP ( 'Table'[Service_attended_date], Datedim[Date] ),
        FiltTable
    )