cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular 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
Regular Visitor

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!

View solution in original post

3 REPLIES 3
Community Champion
Community Champion

@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 Super User!




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

Regular Visitor

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!

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors