Counting same event with two dates using same Dim Date and USERELATIONSHIP

[Anonymous]

Hello,

I need to retrive the number of Due events for month/year, retrieve also the number of events done on the same month/year. 

In my dataset I have two tables

1) Maintenance_Jobs_Archive

2) Dim_Date

Maintenance_Jobs_Archive have two relationships with Dim_date:

1) Active [Due Date]

2) Inactive [Done Date]

I'm Creating two measures using "Userelationship"

1)# Done Jobs (Archive) = CALCULATE(COUNT(VW_MAINTENANCE_JOBS_ARCHIVE[Job ID]), USERELATIONSHIP(VW_MAINTENANCE_JOBS_ARCHIVE[Done Date], 'T_BASS_DIM_DATE (Jobs)'[Date]),FILTER(VW_MAINTENANCE_JOBS_ARCHIVE,VW_MAINTENANCE_JOBS_ARCHIVE[Maintenance Type]="Scheduled Maintenance"))

2)
# Due Jobs (Archive) = CALCULATE(COUNT(VW_MAINTENANCE_JOBS_ARCHIVE[Job ID]), USERELATIONSHIP(VW_MAINTENANCE_JOBS_ARCHIVE[Due Date], 'T_BASS_DIM_DATE (Jobs)'[Date]),FILTER(VW_MAINTENANCE_JOBS_ARCHIVE,VW_MAINTENANCE_JOBS_ARCHIVE[Maintenance Type]="Scheduled Maintenance"))

When I had the two measures in to a Matrix visualization, the result is not the expected for the Inactive Relationship. In the Done Date measure just return the count were the Jobs were Due and Done on the same date.

the due jobs count it's correct show the done jobs are just counting the number of due date is the same as done date

[Anonymous · Accepted solution]

Just to let you know that did find the issue.

The solution is not using the "Filter" function from the measure. 

# Done Jobs (Archive) = CALCULATE(COUNT(VW_MAINTENANCE_JOBS_ARCHIVE[Job ID]), USERELATIONSHIP(VW_MAINTENANCE_JOBS_ARCHIVE[Done Date], 'T_BASS_DIM_DATE (Jobs)'[Date]))

# Due Jobs (Archive) = CALCULATE(COUNT(VW_MAINTENANCE_JOBS_ARCHIVE[Job ID]), USERELATIONSHIP(VW_MAINTENANCE_JOBS_ARCHIVE[Due Date], 'T_BASS_DIM_DATE (Jobs)'[Date]))

So if  you use USERELATIONSHIP, dont user "Filter"