Help Needed Iterating through Parent/Child Relationships in Table to get Values and Group Membership

[julesdude]

Hi everyone,

I have three tables in my data model:

1. Investment

Legal Entity Reference	Investment Name	Business Number
Investment A	I_A
Investment B	I_B
Investment C	I_C	F0008
Investment D	I_D	F0009
Investment E	I_E	F0010
Investment F	I_F	F0011

2. Equity Participation

Investor Reference	Investment Reference	Ownership Percentage	Start Date	End Date
	Investment C
Investment C	HDF_SSS	1
Investment C	HDF_DD_SD	0.5
HDF_SSS	HDF_NO1	0.66
HDF_NO1	HDF_222	1	01/01/2020
HDF_NO1	HDF_333	0.49
HDF_222	HDF_Heka_Hold	0.33
HDF_222	HDF_Bon_Hold	0.33	01/01/2022
HDF_222	HDF_Serleet_Hold	0.33

3. Transaction

Owner Entity Reference	Asset Reference	Amount
HDF_DD_SD	JIM222	54354
HDF_DD_SD	JIM223	55533
HDF_DD_SD	JIM224	2222
HDF_DD_SD	JIM225	2432
HDF_333	AXA2021	4342
HDF_333	AXA2022	423
HDF_333	AXA2023	1167
HDF_Heka_Hold	BOMDS001	8888
HDF_Heka_Hold	BOMDS002	474
HDF_Heka_Hold	BOMDS003	54354356
HDF_Heka_Hold	BOMDS004	4444
HDF_Heka_Hold	BOMDS005	2222
HDF_Bon_Hold	SEL1	88867
HDF_Bon_Hold	SEL2	45477
HDF_Serleet_Hold	AIT202	35435
HDF_Serleet_Hold	AIT500	5422
HDF_Serleet_Hold	AIT222	4546
HDF_Serleet_Hold	AIT503	7574577

They have a relationship in the following way:

I would like to be able to do the following two things:

1. Get the [Ownership Percentage] of an [Asset Reference]:

a) Be able to look up an [Asset Reference] from the Transaction table, or any table in my model that might contain an Asset Reference column and an associated value next to it
b) Check the [Owner Entity Reference] in the Transaction table to get the group the Asset belongs to
c) Match this group name against the name in the [Investment Reference] column in the Equity Participation table
d) Ensure that the Start Date in Equity Participation is before my measure [as of date] (which is basically a user selectable date picker) in the report) OR that the [Start Date] is blank, AND that the [End Date] column is after my [as of date] measure if there is date there OR that it is blank.
e) Return the [Ownership Percentage] amount so that this amount can be applied to any value I wish to apply the percentage to

2. This is slightly harder. I have a dropdown filter in my report that lists all [Legal Entity Reference] options from the Investment table in the data model (above). When an option or options are selected, I would like my matrix table underneath to have my rows column list the [Legal Entity Reference] at top level, and expandable all the child items within this group name listed - so all the Asset References that are associated with this. You will notice though that unfortunately, to get through this parent/child relationship, you need to match the same Legal Entity Reference name against the [Investor Reference] and then take the [Investment Reference] on the same row and use that to filter the [Investor Reference] and keep iterating this way in the table to 'unpack' all the child references until the Investment Reference matches to an entry in the [Owner Entity Reference] column of the Transaction table. This then links to the [Asset Reference] in the table on the same row to give us the assets linked to this group.
An example of this: To get all the Asset Reference values if I select Investment C from the drop down in my report. Logic would match Legal Entity Reference to Investor Reference in Equity Participation table. There are 2 results:

Investor Reference	Investment Reference
Investment C	HDF_SSS
Investment C	HDF_DD_SD

Both HDF_SSS and HDF_DD_SD then need to be looked up in the [Investor Reference] column to see if there are any child [Investment Reference] results. There is not for HDF_DD_SD as this matches an entry in the [Owner Entity Reference] column of the Transaction table, and has 4 Asset References:

Owner Entity Reference	Asset Reference
HDF_DD_SD	JIM222
HDF_DD_SD	JIM223
HDF_DD_SD	JIM224
HDF_DD_SD	JIM225

These Asset References need to be part of the list that is displayed in my first row column in my matrix table for my Investment C drop down selection. The assets are all linked to the Owner Entity Reference.

In addition to the above assets, there are others that need to be unpacked and added. The second result was HDF_SSS. To iterate through that parent/child relationship, you would have to cycle/filter through the Investor Reference column, taking the result of the Investment Reference then using the Investment Reference in the Investor Reference column and so forth until the Investment Reference matches an Owner Entity Reference:
HDF_SSS > HDF_NO1
HDF_NO1 > HDF_222
HDF_NO1 > HDF_333

HDF_222 > HDF_Heka_Hold

HDF_222 > HDF_Bon_Hold

HDF_222 > HDF_Serleet_Hold

We now have all the end child relationship names to use in the [Owner Entity Reference] of the Transaction table to complete the list of Asset Reference values in the first row column of the table matrix. In the end, it would like:

Investment C	JIM222
	JIM223
	JIM224
	JIM225
	AXA2021
	AXA2022
	AXA2023
	BOMDS001
	BOMDS002
	BOMDS003
	BOMDS004
	BOMDS005
	SEL1
	SEL2
	AIT202
	AIT500
	AIT222
	AIT503

How can the above two requirements be tackled? I'm stuggling because I know that I would tackle this in Excel with some macro utilising looping, but no such easy way to iterate using DAX.

[lbendlin]

Your table relationships seem off. Please confirm.

The Equity participation percentages don't seem to add up. Please check - for example where are the remaining 33% for HDF_SSS ?

[julesdude]

Hi @lbendlin 

Thanks for taking a look. Yes, the Ownership Percentage column in Equity Participation table will sometimes show a proportionate amount and it won't always equal to 100% in the group it belongs to. This is correct because it doesn't always add up to 100%. It is a case of capturing the appropriate Ownership Percentage so that it can apply that percentage to any value related data against the asset reference.

[lbendlin]

Your data doesn't seem to have the coherence that is required for a hierarchy calculation. You may have to do these calculations manually.