Calculation Items Parent (For Hierarchical Slicer)

[JeanMartinL]

Hello,

I'm currently using Calculation Groups to drive a slicer that selects between different measures with different formatting options and this is working great so far.

However I'd like to go a step further and change my slicer to be hierarchical with 2 levels, when the top level is selected I would like to display a different measure (which might be a sum or an average of the child items, or something else entirely depending on the context).

For example, let's create these simple measures:

M_1_1 = 1000
M_1_2 = 2000
M_2_1 = 100
M_2_2 = 200
M1_Total = [M_1_1] + [M_1_2]
M2_Total = [M_2_1] + [M_2_2]
M_Value = BLANK()

Then a very simple Calculation Groups that basically just returns the appropriate measure:

This works fine with a standard slicer.

However what I'm trying to do is regroup "1_1" and "1_2" under a "M1" level and so on.

By adding a calculated column to "CG" I can show the slicer like I want it and it even works at the child level, but of course it doesn't do anything when you select the parent:

What I would like in this scenario would be to display the "M1_Total" measure when the parent is selected.

I tried a couple ideas to make this work, for exemple having 2 different Calculation Groups with a relationship between them (it's not allowed) but I only came out with 2 options that works but are not perfect.

1) Having the total as a child item

This work but I don't find it really elegant, if the parent is selected we still get a BLANK()

2) Routing to the appropriate measure in the M_Value measure.

M_Value = 
    SWITCH(SELECTEDVALUE(CG[Parent]), 
        "M1", [M1_Total], 
        "M2", [M2_Total], 
        BLANK())

Again this works and this time even at the parent level. But it feels like I'm using 2 different techniques to accomplish a single goal. Also, another issue with this method is that I can't use different formatting options for M1_Total and M2_Total, one of the reasons why I'm using Calculated Groups in the first place.

I'm still new to Calculation Groups so I was wondering if anyone has a better idea on how to accomplish this.

Thanks!

[amitchandak]

@JeanMartinL , You can use isfiltered. But in reverse order. When child is filtered then parent is

Switch (True() ,

isfiltered(Table[Name]), [Sum Measure],

isfiltered(Table[Parent]), [Avg Measure]

)

Refer

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[JeanMartinL]

Hello @amitchandak,

Thank you for your response. Please tell me if I'm misunderstanding your post but that only seems like a variation on my second solution without addressing its shortcomings.

It might not have been clear enough in my post, especially since I didn't implement it in the example, but one of the big reasons that I am using a Calculation Group in the first place is to use variable formatting on the measure selected. For example, some would be integers, some would show 2 decimals, some would be percentages, some would be in Ks or Ms, etc.

I can accomplish this goal with the calculation items at the child level but not the parents, which is why this post is titled “Calculation Items Parent (For Hierarchical Slicer)”. My first solution kind of work because it's at the child level but I find it inelegant. My second solution (and if I’m not mistaken yours as well) works for the values but not the formatting since it’s not using a calculation item.

Thanks again for your help!