Table Relationship Query USERELATIONSHIP()

[jaime_blackwell]

Hi Community,

I am having some difficulty using USERELATIONSHIP function in Power BI and I'm hoping I can get some assistance. I have an inactive relationship between 2 tables, and I want to activate it for the calculation of a measure. I have used the attached formula, however if I simply activate the relationship, the values appearing in my visualisations are different to the values calculated by the measure. 

Shouldn't USERELATIONSHIP() behave as if the relationship were active?

Thank you. 

[ Tickets_Resolved ] =

CALCULATE (

COUNTX( AllPPMTickets, AllPPMTickets[Ticket_id] ),

USERELATIONSHIP(AllPPMTickets[Resolved_date_time], Dates[Date] ),

FILTER( AllPPMTickets , AllPPMTickets[Resolved_status] = TRUE )

)

[CNENFRNL]

Hi, @jaime_blackwell , based on your description, I think the order of evalution in CALCULATE matters.

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

filters evaluate parallelly, which means, in your case, FILTER( AllPPMTickets , AllPPMTickets[Resolved_status] = TRUE ) doesn't get affected by USERELATIONSHIP(AllPPMTickets[Resolved_date_time], Dates[Date] ); it evaluates under the former active relationship.

You may try this measure,

[ Tickets_Resolved ] =
CALCULATE (
    COUNTX ( AllPPMTickets, AllPPMTickets[Ticket_id] ),
    CALCULATETABLE (
        FILTER ( AllPPMTickets, AllPPMTickets[Resolved_status] = TRUE ),
        USERELATIONSHIP ( AllPPMTickets[Resolved_date_time], Dates[Date] )
    )
)

[jaime_blackwell]

Hi @CNENFRNL ,

Thanks so much for that suggestion, however the values produced by the measure you provided are the same. I figured that the order of the filter and userelationship functions would be irrelevant because the relationship does not affect the filtered column, so the result should be the same either way. 

The measure is basically behaving as though the USERELATIONSHIP() function isn't working at all. 

[jaime_blackwell]

Update: I was able to resolve the issue using the below

[ Tickets_Resolved ] = 
   
 VAR 
    PPMTickets = CALCULATETABLE (
                FILTER ( AllPPMTickets, AllPPMTickets[Resolved_status] = TRUE ),
                USERELATIONSHIP ( AllPPMTickets[Resolved_date_time], Dates[Date] )
            )
            
RETURN
    COUNTROWS( PPMTickets ) 

Thank you again for your help!