Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |