Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdogcisco
Resolver I
Resolver 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...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.