Forum Discussion
Using USERELATIOSHIP with a FILTER - Not working
Hey,
I have a table with a list of home repairs. I have the date the job was raised and the date it was complete.
My date table has an active relationship to the job raised date column, and an inactive one to the job completed date.
I have 2 measures calcuating both the Number of Jobs Raised and Number Of Jobs Completed. The job raised one works fine as it uses the current active relationship. On my page I have a Date slicer from the Date table.
For the Number of Jobs Completed I used USERELATIONSHIP to temp activate the inactive relationship. This gives me back the correct number. However, I need to filter this to only show specific types of jobs, but when I add that into the measure it seems to use both relationships between the job table and date table.
I can add a filter to the visualization and it works, but I need to use this measure a lot and this doesnt seem like the correct way to do it. Here is my measure. Hope you can help 🙂
M Total Completed Jobs =
CALCULATE (
COUNTX (
Repairs_Main,
Repairs_Main[Job Number as integer]),
USERELATIONSHIP (
Repairs_Main[Date Job Completed (Date Only)],
'Date'[Date]),
FILTER (
Repairs_Main,
Repairs_Main[Repair Priority Bracket] ="Response")
)
Hi jp_fondu
This is related with the context and how it's applied, in this case you first need to apply the filter to the table of the countx and then the USERELATIONSHIP.
Try the following measure:
M Total Completed Jobs = CALCULATE ( COUNTX ( FILTER ( Repairs_Main, Repairs_Main[Repair Priority Bracket] = "Response" ), Repairs_Main[Job Number as integer] ), USERELATIONSHIP ( Repairs_Main[Date Job Completed (Date Only)], 'Date'[Date] ) )
2 Replies
- MFelixSuper User
Hi jp_fondu
This is related with the context and how it's applied, in this case you first need to apply the filter to the table of the countx and then the USERELATIONSHIP.
Try the following measure:
M Total Completed Jobs = CALCULATE ( COUNTX ( FILTER ( Repairs_Main, Repairs_Main[Repair Priority Bracket] = "Response" ), Repairs_Main[Job Number as integer] ), USERELATIONSHIP ( Repairs_Main[Date Job Completed (Date Only)], 'Date'[Date] ) )- jp_fonduNew Member
Excelente. Thanks mate, worked a charm. Appreciate taking the time to answer my query