Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
)
Proud to be a Super User!
Paul on Linkedin.
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!
@v-kelly-msft I have to use userelationship as there is already an active relationship between Datedim & my table on a different column.
@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
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
)
Proud to be a Super User!
Paul on Linkedin.
@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"}
@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
)
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |