User Relation Not working in filter inside calculate function

[jeffrock]

Hi ,

I am using user relation function in filter inside calculate function 

it is throwing me error saying 'USERELATIONSHIP function can only be used in the CALCULATE function.'

I have created inactive relation between 'instance number ' & 'planned_Number' many to many

I am creating this measure in Routes Table

Count = CALCULATE(DISTINCTCOUNT(DEADLINE[WI_ID]), FILTER(DEADLINE, USERELATIONSHIP(ROUTES[Assigned_Vehicle], DEADLINE[INSTANCE_NUMBER]) = DEADLINE[INSTANCE_NUMBER] && USERELATIONSHIP(ROUTES[Date2], DEADLINE[DATE_RELEASED]) > DEADLINE[DATE_RELEASED] && USERELATIONSHIP(ROUTES[Date2], DEADLINE[Date_Delivered2]) < DEADLINE[Date_Delivered2]))

Please let me know if required any changes

Thank you

[v-tangjie-msft · Accepted solution]

Hi @jeffrock ,

Please try this measure.

Count = CALCULATE(
    DISTINCTCOUNT(DEADLINE[WI_ID]),
    USERELATIONSHIP(ROUTES[Assigned_Vehicle], DEADLINE[INSTANCE_NUMBER]),
    USERELATIONSHIP(ROUTES[Date2], DEADLINE[DATE_RELEASED]),
    USERELATIONSHIP(ROUTES[Date2], DEADLINE[Date_Delivered2]),
    FILTER(
        ALL(DEADLINE),
        max(ROUTES[Assigned_Vehicle]) = max(DEADLINE[INSTANCE_NUMBER]) &&
        max(ROUTES[Date2]) > max(DEADLINE[DATE_RELEASED]) &&
        max(ROUTES[Date2] )<max( DEADLINE[Date_Delivered2])
    )
)

You can refer to the following documents that may be helpful to you:

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Power BI Userelationship function with example - Learn DAX

Using two or more USERELATIONSHIPs - Microsoft Power BI Community

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.