Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

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's avatar
    Anonymous
    8 years ago

    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"

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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"