Forum Discussion
return value from directly unrelated table
After a few hours of a break, it was remarkably simple in the end!
EquityPcnt:= CALCULATE(SUM(dPartnershipEquities[Equity]), FILTER(dPartnershipEquities, dPartnershipEquities[partnershipID]=MAX(fTransactions[PartnershipID])&& dPartnershipEquities[PartnerID]=MAX(dPartners[PartnerID])))
I just couldn't fathom it. Staring at it for a prolonged period didn't seem to help!
Hi,
Thanks for you response!
I'm trying to see the difference in the table that you've posted compared to my own? Is it just that you've included equities that add to greater than 1 in P1? If so, I'm not too concerned about that for this particular report (there are other controls over those tables that take care of that). In your example, if fTransactions has two records with P1 PartnershipID of 1000 and 1500 then I'd expect to see
Selecting prtnr1
fTransactions
id-----partnershipID----Gross----[EquityForPrtnr1]---[CalculatedNet]
1------P1----------------1,000----0.5-------------------500
2------P1----------------1,500----0.5-------------------750
Selecting prtnr2
fTransactions
id-----partnershipID----Gross----[EquityForPrtnr2]---[CalculatedNet]
1------P1----------------1,000----0.75-----------------750
2------P1----------------1,500----0.75-----------------1,125
I suppose I require the Slicer to perform that "on the fly calculation of Net value, rather than creating a Net column of Net Value in advance (i.e the two tables combined would appear in my fTransactions table with an additional column for partner - this is what I'm trying to avoid).
After a few hours of a break, it was remarkably simple in the end!
EquityPcnt:= CALCULATE(SUM(dPartnershipEquities[Equity]), FILTER(dPartnershipEquities, dPartnershipEquities[partnershipID]=MAX(fTransactions[PartnershipID])&& dPartnershipEquities[PartnerID]=MAX(dPartners[PartnerID])))
I just couldn't fathom it. Staring at it for a prolonged period didn't seem to help!