Forum Discussion
return value from directly unrelated table
Hi,
First time on here, so excuse if I've broken any rules. I've had a search through the various threads for something that solves my issue but can't find anything that works. What I'm trying to do possibly isn't possible, but I'd like to be sure before I pursue other options.
I have tables, as follows:
fTransactions
id-----partnershipId----Amount
1------P1----------------1,000
2------P1----------------1,500
3------P2----------------1,000
4------P2----------------2,000
dPartnerShips
partnershipId----partnershipName
P1----------------PartnershipNo1
P2----------------PartnershipNo2
dPartnerShipEquities
partnershipId----partnerID--Equity
P1----------------prtnr1------0.5
P1----------------prtnr2------0.5
P2----------------prtnr2------0.25
P2----------------prtnr3------0.25
P2----------------prtnr4------0.25
P2----------------prtnr5------0.25
dPartners
partnerID----PartnerName
prtnr1--------PartnerNo1
prtnr2--------PartnerNo2
prtnr3--------PartnerNo3
prtnr4--------PartnerNo4
prtnr5--------PartnerNo5
I have relationships between:
fTransactions/partnerID->dPartnerships;
dPartnerships/partnershipID->dPartnerShipEquities/partnershipId;
dPartnerShipEquities/partnerID->dPartners/partnerID
I want to multiply fTransactions/Amount * dPartnerShipEquities/Equity to get the netPartnerShare. That is easy enough if I merge tables and expand the results. However, I was hoping to create a measure that would do this for me so that I don't have to expand my data set (there are lots of transactions and lots of partnerships with more than one partner) significantly. Is there a DAX formula with which I can return the equity, and dynamically change that using a Slicer on my "Partner" code in order to get a net value for a selected Partner?
Apologies if this is something stupidly easy, it's probably one of those that the more I look at it the more I confuse myself.
Thanks in advance,
Rico.
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!
3 Replies
Anonymous Just to confirm, each partnershipids have mutliple partnerids and equities. So it will be same equity for all partnerids under same partnershipid.
Example : fTransactions has two records with P1 PartnershipID, so you need to multiply 1000 and 1500 with 0.5. But what if by any chance your data is like this. Then how do you want to handle this ?
dPartnerShipEquities
partnershipId----partnerID--Equity
P1----------------prtnr1------0.5
P1----------------prtnr2------0.75
P2----------------prtnr2------0.25
P2----------------prtnr3------0.25
P2----------------prtnr4------0.25
P2----------------prtnr5------0.25
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!
