cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RicoS Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RicoS Frequent Visitor
Frequent Visitor

Re: 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!

3 REPLIES 3
Super User
Super User

Re: return value from directly unrelated table

@RicoS 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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





RicoS Frequent Visitor
Frequent Visitor

Re: return value from directly unrelated table

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).

RicoS Frequent Visitor
Frequent Visitor

Re: 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!