Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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.
@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]))
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |