User Profile
JPScotland
Helper I
Joined 6 years ago
User Widgets
Contributions
Filter not picking up values
I have 3 tables [jobbing_Data_General], [Completed_Jobs], and [Date]. [jobbing_Data_General] is 1:1 with [Completed_Jobs] on attribute [Job Number as integer]. [jobbing_Data_General] is 1:many with [Date] on attrinute [Date Job Raised (Date only)] [Completed_Jobs] is 1:many (inactive) on attribute [Date Job Completed (Date Only)] So If I want to count the number of jobs completed and use the Date as a slicer, I need to use the USERELATIONSHIP function to go direct to [Date]. [jobbing_Data_General] has a relationship with [Date] on Jobs Raised, not Complete so I need to avoid going thorugh this table as it messes up the results. Count Completed Jobs = CALCULATE ( COUNTX ( Completed_Jobs, Completed_Jobs[Job Number as integer]), USERELATIONSHIP ( Completed_Jobs[Date Job Completed (Date Only)], 'Date'[Date] ) ) This works fine, but now I need to add a filter that comes from the [jobbing_Data_General] table called [Job Priority Code]. So I add this into the formula from above: - Count of Emergency Jobs Complete = CALCULATE ( COUNTX ( Completed_Jobs, Completed_Jobs[Job Number as integer]), USERELATIONSHIP ( Completed_Jobs[Date Job Completed (Date Only)], 'Date'[Date] ), FILTER ( Jobbing_Data_General, Jobbing_Data_General[Job Priority Code] = "3" ) ) The strange this is is will produce only results for one [Job Priority Code] but not any others. I just get BLANK. I thought it was the data so I started a fresh report and it does the same but on a different [Job Priority Code]. Its like its only able to use it if it appears at the top of the table or something? I basically need to pull off how many jobs are completed by different priority codes. Any help would be appreciated. Cheers, JP
