Parameter slicer hierarchy to be 100% (nested) (cascaded)

[mrothschild]

Hi,

I'm trying to assess different investment allocations and would like to build a hierarchy slicer that slices the numbers below it.

As an example, say an investment portfolio can invest in 4 broad categories - equities (stocks), fixed income (bonds), real estate, alternatives.

But within equities, you also have public and private.  And within public, you also have US and rest of world.

Below is what I'm hoping to achieve.

___________________________

[Slicer 1]

Equities      ------> select 60 from a dropdown list of 0 - 100

Public  ------> select 50 from a dropdown list of 0 - 60 [maximum of parent level]

US Equities                      ---------------> Select 30 from a dropdown list of 0-50 [maximum of parent level]

Rest of World Equities     ---------------> Defaults to 20 based on above or from a dropdown list of 0-20 [maximum of parent level less prior selection on this child level]

Private  ------> defaults to 10 based on above from a dropdown list of 0-10 [maximum of parent level less prior selection on this child level]

[Slicer 2]

Fixed Income ---------> Select 40 [maximum of 100 less prior selection on this level]

Public     ---------> Select 30 [select from dropdown of 0-40, which is maximum of parent level]

Barclays Aggregate          ----------> Select 15 [defaults to maximum of parent level from a dropdown of 0-30]

Barclays High Yield          -----------> Defaults to 15 to from a dropdown of 0-15

Private    ---------> Defaults to 10 based on above from a dropdown of 0-10

[Slicer 3]

Real Estate   ----------> "grayed out" because above adds to 100

[Slicer 4]

Alternatives ----------> "grayed out" because above adds to 100

_________________________________________

So assume a 60% allocation to equities, what I'm trying to achieve is a Parameter Slicer where Equities is at the top, and when selected the next hierarch is "Public equities" which limits the maximum to 60, and then nested beneath is the selection for US and Rest of World, where the maximum would be 60.

Assuming the above makes sense, is this possible?

Thanks in advance!!

[AlexisOlson · Accepted solution]

If you do the math right, you can automatically build in some of the filtering.

For example, this returns all possible configurations for equities at 1% granularity:

Equities = 
VAR N = 100
RETURN
    ADDCOLUMNS (
        GENERATE ( 
            GENERATE (
                SELECTCOLUMNS ( GENERATESERIES ( 0, N ), "Equities", [Value] ),
                SELECTCOLUMNS ( GENERATESERIES ( 0, [Equities] ), "Public", [Value] )
            ),
            SELECTCOLUMNS ( GENERATESERIES ( 0, [Public] ), "US", [Value] )
        ),
        "Private", [Equities] - [Public],
        "Global", [Public] - [US]
    )

 Check out my attached pbix for more detail. I've got it looking like this:

[mrothschild]

Hey @AlexisOlson 

I've tried to nest more GENERATEs in there to accommodate additional "Children" (e.g., Public US, Public Asia, Public Europe, Public Emerging Markets" and I keep getting "Insufficient memory" errors.  I've tried using VAR to generate parent tables and then keep nesting them with the virtual table created prior.  

Do you have any suggestions as to how to go about this in a more memory-efficient manner?  Maybe CROSSJOIN or TREATAS - both of which I only have passing familiarity and may not work at all, but look like they have potential.

Thanks!

[AlexisOlson]

I wouldn't recommend going more than 3 or 4 layers deep due to the combinatorial explosion in the size of the table. There are no tricks I know of to tame this in a single table.

For N = 100, the size of the table is the binomial coefficient C(d, 100) where d is the depth.

1 layer: 101 rows

2 layers: 5,151 rows

3 layers: 176,851 rows

4 layers: 4,598,126

5 layers: 96,560,646

6 layers: 1,705,904,746

etc.

In the file I uploaded, I avoided this explosion by breaking up the slicers into multiple tables with measure filters to limit the slicer display (see e.g. [LimitFI] is 1 measure filter for my % Fixed Income slicer).

[mrothschild]

@AlexisOlson 

Thanks so much.  This is enough to get me started and do some damage.

[lbendlin]

This might be possible with a series of "What-if" parameters.  Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.