Calculate(sum(filter returning sum all records not filter criteria

[PurdiMark]

Hi Community

I have 2 tables Stock and Quotes. Both tables have an Item field, however there are multiple occurances of the same item in both tables so i cannot set up a relationship. I am trying to add a column to Stock that sums the Quantity in Quotes where the Item in Quotes = Item in Stock AND probability in Quotes >=70% (0.7). Seems straight forward however the filter appears to be ignoring Item. What am i missing here?

CALCULATE(sum(Quotes[Quantity]),filter(Quotes,Quotes[Item]=[Item] && Quotes[Probability]>=0.7))

[AlB · Accepted solution]

Hi @PurdiMark 

The logic looks correct but try:

CALCULATE(sum(Quotes[Quantity]),filter(Quotes,Quotes[Item]=Stock[Item] && Quotes[Probability]>=0.7))

since then engine is probably assuming that [Item] is actually Quotes[Item] instead of Stock[Item]

Always use the name of the Table when referring to columns

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PurdiMark]

AIB, thankyou. skipping table names is a poor habit i know 🙄, but 'normally' code/script etc understands (well thats my excuse)!