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]

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

     

1 Reply

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