USERELATIONSHIP with Filter

[Anonymous]

Hey all, 

I'm having trouble determining why my DAX doesn't return the correct answer.

The active relationship is  Calendar[Calendar Date] -> Service Agreement[Agreement Start Date Local]

However there is another column on the Service Agreement table, [Agreement End Date Local] which I'm trying to switch to when getting the row count for this measure.

I'm testing one user who has the following start and end dates:

CALCULATE(
DISTINCTCOUNT('Service Agreement'[Service Agreement Sk]),
USERELATIONSHIP(Calendar[Calendar Date], 'Service Agreement'[Agreement End Date Local]),
FILTER(
'Service Agreement',
'Service Agreement'[Agreement End Date Local] <= [End of Current Month] &&
'Service Agreement'[Agreement Event Type Sk] = 5
)
)

End of Current Month = EOMONTH(TODAY(), 0)

Why doesn't my formula return the correct date?

[Anonymous · Accepted solution]

Well, I'm dumb...
All I needed to do was drop the USERELATIONSHIP within the measure and then reference it within another measure

Expired = 
CALCULATE(
DISTINCTCOUNT('Service Agreement'[Service Agreement Sk]),
FILTER(
'Service Agreement',
'Service Agreement'[Agreement End Date Local] <= [End of Current Month] &&
'Service Agreement'[Agreement Event Type Sk] = 5
)
)

CALCULATE( [Expired],
USERELATIONSHIP('Service Agreement'[Agreement End Date Local], Calendar[Calendar Date])
)