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

 

 

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
    )

     

     

7 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

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

      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's avatar
        PaulDBrown
        Community Champion
        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
    Community 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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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