Forum Discussion

jaime_blackwell's avatar
jaime_blackwell
Helper I
5 years ago

Table Relationship Query USERELATIONSHIP()

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 )

)

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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's avatar
      jaime_blackwell
      Helper I
      5 years ago

      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's avatar
        jaime_blackwell
        Helper I
        5 years ago

        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!