Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MPG758
Frequent Visitor

DAX - Organizational Hierarchies - Show/Hide Hierarchy Subtotal Dependent on Slicer

Hello everyone!

 

we are trying to build a report, in our organization, that pivots information about a specific group of people.

The model is being implemented, at this point, in Excel but will be migrated to Power BI in its due time.

 

The model we are implementing, is following the pattern posted by Marco Russo here with one only difference: it has more hierarchies levels.

The behavior of the model we are implementing is just the same as it is in the pattern above, however we would like to hide subtotals from the parent hierarchies when a specific hierarchy level is selected.

 

So, to make this little bit more clear, imagine that you have your model build like this (nothing selected on slicers):

 

NoSlicerFilter.PNG

 

When no slicers are selected the pattern works exactly how we want, as shown in the upper picture.

However, when we select a person from a lower level hierarchy, the behavior does not works how we would like.

 

In the lower picture, we show a selection of a person in the second level of the hierarchy, and we would like to build dax formulation that won't show the parent hierarchy subtotal:
SlicerFilter.PNG

 

So, our question is: how can one build a dax formulation, that shows brad's subtotal and show's blank's for his parents hierarchies (in this case its bill witch is highlighted) ?

 

I hope my explanation is clear enough, but if not, please ask me for more information.

Thanks in advance for your help and for reading this! Smiley Very Happy

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @MPG758,

 

I write a formula and try to remove the summary value at top level, but it seem only works when its child node larger than 1.

Test = 
VAR current_level =
    COUNT ( Nodes[NodeKey] )
VAR all_level =
    COUNTX (
        FILTER ( ALLSELECTED ( Nodes ), [Level1] = MAX ( [Level1] ) ),
        [NodeKey]
    )
RETURN
    IF ( current_level <> all_level, SUM ( Nodes[Amount] ) )

2.PNG

 

Maybe you can add a condition to check the current level if it is the top level and replace it to blank.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft, and thanks for your reply/help!

 

I might be wrong, but i think he formulation you provides is not exactly how i would like the model to behave.

I only want to remove the top level total when, and only when, a person is selected/filtered in the slicer.  Is this possible? I belive it is. 🙂

 

Also, this "(...) Maybe you can add a condition to check the current level if it is the top level and replace it to blank. (...)", is where i'm having troubles with. 🙂


MPG758
Frequent Visitor

Hi everyone!

 

I still struggle to find a solution for this issue.

I wonder if someone else has anything to add.

 

Thanks 🙂

MPG758
Frequent Visitor

Up! Up!

 

There must be a way of resolving this issue.

Does anyone has anything to add?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.