cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdogcisco
Helper I
Helper I

USERELATIONSHIP not working in calculated column, is this possible?

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?
1 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.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.