USERELATIONSHIP not working in calculated column, is this possible?

[jdogcisco]

Using Salesforce data from the Lead and Opportunity objects/tables. Currently the tables have no defined relationships. I am counting the number of Lead rows that were converted into an Opportunity row where the Opportunity Stage Name field is either Won, Lost, or Open. I get accurate counts, but the counts show up by the Lead record created date and I would like to count based on converted date.  There are many date related fields so many are not active so I I am trying to use the USERELATIONSHIP for the inactive relationship to the Calendar[date] table. But it seems it's ignored.

Here is my calculated column:

Count Converted Opportunities Won = CALCULATE(COUNTROWS('Opportunity'), FILTER('Opportunity', Opportunity[Id] = 'Lead'[ConvertedOpportunityId]), Opportunity[StageName] = "Closed Won", USERELATIONSHIP('Lead'[ConvertedDate], 'Calendar'[Date]))

I created similar columns for Lost and Open

I have a Table visual with Calendar[date] and the calculated columns mentioned above. The counts are accurate, but they are listed by the Lead created date (which is the active relationship to the Calendar table). I would like the counts to show up by the converted date.

Lead Table has the following fields
id
createdDate
convertedDate

convertedOpportunityId

Opportunity Table has the following fields
id

createdDate
stageName

Is this possible?

[amitchandak]

@jdogcisco , Why do need userelation in a column , That you need in measure

column like

Count Converted Opportunities Won = CALCULATE(COUNTROWS('Opportunity'), FILTER('Opportunity', Opportunity[Id] = 'Lead'[ConvertedOpportunityId]), Opportunity[StageName] = "Closed Won" )

measure like

Count Converted Opportunities Won = CALCULATE(COUNTROWS('Opportunity'), FILTER('Opportunity', Opportunity[Id] = 'Lead'[ConvertedOpportunityId]), Opportunity[StageName] = "Closed Won", USERELATIONSHIP('Lead'[ConvertedDate], 'Calendar'[Date]))

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[jdogcisco · Accepted solution]

Ah, I didn't separate a column and a measure in my mind until you mentioned it. The measure you proposed didn't work, it complained that 'Lead'[ConvertedOpportunityId] could not be found. This worked for creating the column, but not the measure.  However, after creating the column, I was then able to make a measure and then counta in the new column and use USERELATIONSHIP there in the measure and the data lines up.

I appreciate your reply. It helped me a lot.