USERELATIONSHIP not showing all the records

[Anonymous]

Hello,

I have 3 measures

This returns the number the records created on datumCallDate with a relationship with the datetable 'dwh DimDate'. Joined on DateKey and datumCallDate

Count Aangemeld callDate =
CALCULATE(
COUNT('vw_topdesk_incidenten_dt'[id]),
USERELATIONSHIP('dwh DimDate'[DateKey],vw_topdesk_incidenten_dt[datumCallDate]), FILTER('vw_topdesk_incidenten_dt','vw_topdesk_incidenten_dt'[active] ="yes")) 

This returns the number the records created on datumCompletedDate with a relationship with the datetable 'dwh DimDate'. Joined on DateKey and datumCompletedDate

Count Afgemeld completedDate =
CALCULATE(
COUNT('vw_topdesk_incidenten_dt'[id]),
USERELATIONSHIP('dwh DimDate'[DateKey],vw_topdesk_incidenten_dt[datumCompletedDate]), FILTER('vw_topdesk_incidenten_dt','vw_topdesk_incidenten_dt'[completed] = "yes" && 'vw_topdesk_incidenten_dt'[closed] = "No" && 'vw_topdesk_incidenten_dt'[active]="yes")) 

This returns the number the records created on datumClosedDate with a relationship with the datetable 'dwh DimDate'. Joined on DateKey and datumClosedDate

Count Afgesloten closedDate = 
CALCULATE( COUNT('vw_topdesk_incidenten_dt'[id]),
USERELATIONSHIP('dwh DimDate'[DateKey],vw_topdesk_incidenten_dt[datumClosedDate]), FILTER('vw_topdesk_incidenten_dt','vw_topdesk_incidenten_dt'[closed] = "yes" && 'vw_topdesk_incidenten_dt'[active]="yes"))  

I created my relationships with the datetable

Relationships

The linechart

Linechart

The line Count Aangemeld callDate is showing the right numbers but Count Afgemeld completedDate and Count Afgesloten closedDate and Count Afgesloten closedDate are not.

Let's look at 8/02/2021

 When I do a count on my SQL table I get different numbers

 I can't figure out why. Are my measures wrong?

[jameszhang0805]

Hi @Anonymous,
My friend once met this problem. We were thinking for a long time but we couldn't find the reason. Finally, my friend sent a mail to SQLBI.com, then they replied to us:
So the answer to your question is in the order of evaluations of the steps of Calculate function.
When FILTER is evaluated it still uses the active relationship. After it's been evaluated, the USERELATIONSHIP modifier is executed and when the measure is evaluated the result of the filter that was evaluated before is applied in an AND condition with the filter resulting from the USERELATIONSHIP. To overcome this issue, remove the filtering of all conditional columns with the All function, and wrap Keepfilters outside the Filter function is the safest way to keep the correct result. 
My English is poor, hope you can understand my explanation. If my solution is ok, please click to accept solution.
Thanks.
BR/James Zhang

[jameszhang0805]

I can't guarantee that it will solve the problem, but if you are willing to try

[Anonymous · Accepted solution]

Hey @jameszhang0805 ,

The solution was just deactivating all relationships. CallDate was active that why that number was correct