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
    )

     

     

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
    )