Forum Discussion

JPScotland's avatar
JPScotland
Helper I
4 years ago
Solved

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)]

 

Relationships

 

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

 

 

  • Not sure if this will work but you can try

    Count of Emergency Jobs Complete = 
                    CALCULATE (
                                COUNTROWS (Jobbing_Data_General), 
                                USERELATIONSHIP (
                                                    Completed_Jobs[Date Job Completed (Date Only)], 'Date'[Date]
                                ),
                                Jobbing_Data_General[Job Priority Code] = "3"
                    )

2 Replies

  • Not sure if this will work but you can try

    Count of Emergency Jobs Complete = 
                    CALCULATE (
                                COUNTROWS (Jobbing_Data_General), 
                                USERELATIONSHIP (
                                                    Completed_Jobs[Date Job Completed (Date Only)], 'Date'[Date]
                                ),
                                Jobbing_Data_General[Job Priority Code] = "3"
                    )