Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Userelationship on a calculated column with Filter issue

Hi community, Following is my dataset:                                   My pbi file:  https://1drv.ms/u/s!Ag919_pO_UKrgQhdlDIIlXqro0QP?e=Jjmbph Service_attended_date is a c...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

     

    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
    )

     

     

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

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

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