topic Re: USERELATIONSHIP in a measure in DAX Commands and Tips

USERELATIONSHIP in a measure

Anonymous — Thu, 29 Feb 2024 15:00:06 GMT

I have two tables: 'Deals' and 'Dates' with couple many to one inactive relationships (for example based on 'Deals'[Created Date] and 'Deals'[Won Date]).
I ran two queries in Power BI Desktop using DAX Query View:
1.

EVALUATE { CALCULATE( COUNT( 'Deals'[Deal ID] ), USERELATIONSHIP( 'Dates'[Date], 'Deals'[Won Date] ), FILTER( 'Deals', 'Deals'[Product] = "XYZ" && NOT ISBLANK('Deals'[Won Date]) ) ) }

EVALUATE { CALCULATE( COUNT( 'Deals'[Deal ID] ), FILTER( 'Deals', 'Deals'[Product] = "XYZ" && NOT ISBLANK('Deals'[Won Date]) ) ) }

I am getting two different results, which makes me wondering why if there are no outer filters applied and how does calculate work in the bacground to obtain different outputs here.
I have went through different 'How does CALCULATE work' articles but could not find one that covers my case.
Thank you in advance!

Re: USERELATIONSHIP in a measure

Chakravarthy — Thu, 29 Feb 2024 17:18:48 GMT

Hi Anonymous -

Please try below code:

SOLUTION 1:

===========

When there is Inactive Relationship

EVALUATE

{

CALCULATE(

COUNT( 'Deals'[Deal ID] ),

USERELATIONSHIP( 'Dates'[Date], 'Deals'[Won Date] ),

REMOVEFILTERS('Deals')

KEEPFILTERS(

'Deals'[Product] = "XYZ" &&

NOT ISBLANK('Deals'[Won Date])

)

}

SOLUTION 2:

==================

Remove the Inactive Relationship and use below code

EVALUATE

{

CALCULATE(

COUNT( 'Deals'[Deal ID] ),

TREATAS( VALUES('Dates'[Date]), 'Deals'[Won Date] ),

FILTER('Deals',

'Deals'[Product] = "XYZ" &&

NOT ISBLANK('Deals'[Won Date])

)

}

Re: USERELATIONSHIP in a measure

Anonymous — Fri, 01 Mar 2024 08:41:13 GMT

Hey Chakravarthy,
thank you for your reply, do you know (what Im most interested in) where is the difference in the output in my two queries coming from and why is it happening?

Re: USERELATIONSHIP in a measure

talespin — Fri, 01 Mar 2024 09:32:57 GMT

hi Anonymous

Can you please share sample data with which you are seeing the count difference.