Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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")
)
Solved! Go to Solution.
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] )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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] )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsExcelente. Thanks mate, worked a charm. Appreciate taking the time to answer my query
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |