Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

USERELATIONSHIP with Filter

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] Howe...
  • Anonymous's avatar
    Anonymous
    3 years ago

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

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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