Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

USERELATIONSHIP in a measure

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!

USERELATIONSHIP DAX Measures

3 Replies

  • Chakravarthy's avatar
    Chakravarthy
    Icon for Resolver II rankResolver II
    2 years ago

    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])
    )
    )
    }
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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?

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi Anonymous 

     

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