Countrows from an unrelated table

[Jan_Mack79]

I have a Salesforce Opportunity table and an Opportunity Field History table and I am trying to count the number of times that an opportunity Close Date was pushed out. The tables are not related in my model but I have the opportuinty id in both tables and I have done a lookupvalue column in the field history table as well to get the opportunity number.

I have tried creating a calculated column using the formual in bold below in the the Opportunity table but the reults are showing correct on some records but not others.

Below are 4 examples below, the first and last are incorrect and the middle are correct. I have manully filtered the Field History table for each opportunity number to verify. I have no idea what is casuing these varinaces but also note that the History table is also filtered in the query editor to isolate the Field "Close Date" but even without the filter there are only 108 rows in the the table for the first opportunity example, so no idea where is it getting 171??

Close Date Move Out Count = CALCULATE(COUNTROWS('Close Date History'),FILTER('Close Date History','Close Date History'[Opp Number]=Opportunity[OpportunityNumber__c]))

[Ajendra]

Hey,

At least share some demo-data alongwith desire output.

thanks!

[Wilson_]

Hi Jan,

Dumb question: Why isn't there a relationship between the two tables? Presumably the opportunity number is a primary key in the Opportunity table?

[Jan_Mack79]

When I tried doing that  connecting the Opportunity table to the Field History table it said it was a Many to Many relationship. So then I tried connceting it with the Unique Opp table which is calculted table from the Opportunity table and that said that they have a circular dependancy.

[Wilson_]

Jan,

Got it. Wild guess: Is opportunity 249465 in your Opportunities table nine times?

Anyway, try this instead:

Close Date Move Out Count = 
VAR OppNum = Opportunity[OpportunityNumber__c]
VAR FilteredHistory =
FILTER (
    'Close Date History',
    'Close Date History'[Opp Number] = OppNum
)

RETURN
COUNTROWS ( FilteredHistory )

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Jan_Mack79]

Hi Wilson,

OK Yes there are nine records of opp 249465 in the Opportunity table becasue the releted products are merged with that table and there are nine products for that opportunity, so that makes sense now why some of the opportunities are showing correct becasue those have only 1 product.

I have made the edits to the formula as you suggested but it it still returning the same result 😞

[Wilson_]

Hm, can you please provide an anonymized sample pbix file please, Jan? I'd like to play in it to figure this one out.