topic Re: Table Relationship Query USERELATIONSHIP() in DAX Commands and Tips

Table Relationship Query USERELATIONSHIP()

jaime_blackwell — Wed, 30 Sep 2020 22:40:40 GMT

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 )

)

Re: Table Relationship Query USERELATIONSHIP()

CNENFRNL — Wed, 30 Sep 2020 23:16:08 GMT

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

Re: Table Relationship Query USERELATIONSHIP()

jaime_blackwell — Thu, 01 Oct 2020 00:11:21 GMT

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.

Re: Table Relationship Query USERELATIONSHIP()

jaime_blackwell — Thu, 01 Oct 2020 00:49:17 GMT

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!