USERELATIONSHIP in a measure

[filipbobczuk]

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

2. 

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!

[talespin]

hi @filipbobczuk 

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

[filipbobczuk]

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?

[Chakravarthy]

Hi @filipbobczuk  -

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

)

)

}