Dynamic Hierarchy in Power BI Decomposition Tree Based on Filter Selections

[Mina_PowerBI]

Objective:

I am working on a Power BI report using a dataset from an Excel file (AM Excel Database), and I would like to implement a dynamic, auto-expanding decomposition tree that adjusts its hierarchy path based on the user’s filter selections.

The goal is to create an interactive and intelligent hierarchy experience where users control the decomposition path simply by selecting values from filters, without manually configuring levels inside the decomposition tree visual.

The dataset contains 10 primary filters (dimensions), which are: Division , DSO Region , KPI Customer Cluster , Country Sold To , Country Ship To, Customer Sold To , Customer Ship To , Customer Payer , Production Plant , Company Code 

Billing Document Num.

Customer

Customer Text

Posting Date

Collection Group

Collection Specialist

Profit Center

Trading Partner AR

Trading Partner AR Text

Terms of Payment Key (Days)

Country Trading Partner

Company Code

Order reason

Sales Document Type

Customer Lead Responsible Group(EO/Region/Division)

Customer Lead Responsible Group(EO/Region/Division) TExt

Customer Lead Responsible (SCC)

Customer Lead Responsible (SCC) Text

RB Division

Posting key

FI document type

FI document type Text

Customer Segment

RB-Customer Group

RB-Customer Group Text

RB-Customer Subgroup

RB-Customer Subgroup Text

RB Customer (external)

RB Customer (external) Text

KPI Customer Cluster

KPI Customer

DSO Region

Terms of Payment Key

Terms of Payment Key Text

Sales Organization

Reason code group

Reason code group Text

Reason Code

Reason Code Text

Net due date

Total Receivables in Local Currency

3E+09

4E+07

Thomas, Potts and Lynch

2024-02-09

agency

Daniel Soto

963271

8576538

Ellis, Gross and Contreras

30

ML

3000

city

CR

draw

TV

Michael Jordan

Jewellery designer

hit

01

DZ

strategize front-end partnerships

challenge

A

use

Y

growth

4.99E+08

Martinez LLC

Cluster 1

tend

NY

0001

more

SO3

RCG2

not

RC2

accept

2025-07-27

42830.06

9E+09

5E+07

Johnson Inc

2024-05-08

stay

Christine Higgins

477032

37372903

Hernandez-Adams

30

AM

2000

stage

CR

type

ago

Karen Montoya

Camera operator

trade

11

DR

integrate killer ROI

including

A

past

X

receive

1.51E+08

Rodriguez, Alvarez and Oneill

Cluster 3

already

RI

0002

cut

SO2

RCG1

those

RC2

sure

2025-08-29

34578.66

1E+09

1E+07

Taylor-Stafford

2024-02-05

down

Fernando Jenkins

672790

44352545

Ross, Moreno and Reed

60

SM

3000

sell

CR

Congress

mind

Dwayne Gray

Regulatory affairs officer

maintain

11

DR

seize B2C technologies

fact

B

according

Z

himself

3.3E+08

Callahan, Wolf and Gardner

Cluster 1

resource

ME

0002

Democrat

SO2

RCG1

tell

RC2

themselves

2025-07-11

31233.33

1E+09

9E+06

Wang, Miller and Peterson

2024-07-26

event

Holly Armstrong

156220

18152611

King-James

45

BN

3000

employee

RE

agency

talk

Dr. Adrian Mitchell

Chief Strategy Officer

floor

01

DZ

synergize sticky networks

cultural

B

method

Z

behavior

17643277

Hull, Owens and Kane

Cluster 3

ground

CO

0003

avoid

SO3

RCG2

late

RC1

analysis

2025-07-20

47950.56

3E+09

7E+07

Leach LLC

2025-03-14

whose

Krystal Peterson

17805

2028050

Martin-Jordan

45

PL

3000

into

RE

second

adult

Janet Orr

Air cabin crew

him

40

DR

incentivize collaborative web services

similar

B

remain

Z

else

9.17E+08

Robinson-Gardner

Cluster 2

friend

TN

0001

drive

SO1

RCG1

score

RC1

act

2025-08-05

42431.79

9E+09

8E+07

Dennis Group

2025-07-06

American

Catherine Huffman

930031

3865205

Mendoza, Taylor and Grant

45

MZ

4000

security

RE

difficult

discover

Jessica Ward

Speech and language therapist

into

40

DZ

orchestrate compelling methodologies

here

B

other

X

current

1.73E+08

Evans and Sons

Cluster 3

name

TX

0001

season

SO3

RCG2

mission

RC1

upon

2025-07-27

1862.61

2E+09

7E+07

Bradley, Young and Walker

2024-10-30

audience

Paul Castillo

634148

30516308

Mcdonald-Graham

30

MV

1000

second

OR

accept

tax

Thomas Hogan

Biomedical engineer

it

01

DZ

repurpose customized mindshare

machine

B

wear

Y

house

8.04E+08

Woods Group

Cluster 2

generation

IA

0001

throughout

SO1

RCG1

alone

RC3

structure

2025-07-30

13366.98

Challenge : 

• The decomposition tree hierarchy path should begin with the first selected filter, then proceed to the next selected filter, dynamically — not following a fixed hierarchy.

• This way, users determine the hierarchy on the fly, based on their filter selections, and the decomposition tree automatically respects this path.

Request for Help:

Can someone please guide me on how to:

• Implement this logic in Power BI?

• Possibly use Field Parameters, dynamic measures, or DAX workarounds to simulate this behavior?

• Automatically drive the hierarchy expansion of the decomposition tree based on filter selections?

[johnbasha33 · Accepted solution]

@Mina_PowerBI 

Key Understanding

• The Decomposition Tree visual in Power BI expects static hierarchies.

• But Field Parameters (introduced in 2022) allow dynamic selection of columns as dimensions.

• What cannot be automated directly is:

  • Changing the hierarchy path automatically based on filter selections.

  • Auto-expansion of branches (Power BI still requires user clicks for drill-down).

We can work around this with Field Parameters + slicers.

Step-by-Step Solution

🔧 1. Create a Field Parameter for Dynamic Hierarchy

Go to Modeling > New Parameter > Fields.

Add all 10 dimensions as choices:

Division
DSO Region
KPI Customer Cluster
Country Sold To
Country Ship To
Customer Sold To
Customer Ship To
Customer Payer
Production Plant
Company Code

Rename the parameter as: Dynamic Hierarchy.

This will create:

• A parameter table (Dynamic Hierarchy)

• A DAX column called [Dynamic Hierarchy Fields]

A DAX measure like:

Selected Hierarchy = SELECTEDVALUE('Dynamic Hierarchy'[Dynamic Hierarchy Fields])

Use Dynamic Fields in Decomposition Tree

Add the Dynamic Hierarchy field (from the parameter) into the decomposition tree as your hierarchy level.

Add your measure (like Total Receivables, Count of Customers, etc.) to analyze.

⚠️ Note:

• You can only select one field at a time in decomposition trees.

For multiple levels, the user has to manually expand the hierarchy within the visual, unless you pre-define the field order via slicer control.

Simulate Ordered Path Using a Slicer

To simulate "first this filter, then next," create a multi-select slicer on your Dynamic Hierarchy parameter table.

This allows users to choose the path they want interactively. While Power BI won’t auto-expand based on the slicer, it will enable:

• Controlled inclusion of fields

Dynamic structure driven by user choice

Optional: Create Custom Table for Slicer Order

If needed, create a custom table to track the sequence of hierarchy (using a ranking mechanism):

HierarchyOrder =
DATATABLE(
"Hierarchy", STRING,
"Order", INTEGER,
{
{"Division", 1},
{"DSO Region", 2},
{"KPI Customer Cluster", 3},
...
}
)

Join it to your Dynamic Hierarchy table and sort accordingly. This helps predefine the path in visuals if needed.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Ilgar_Zarbali · Accepted solution]

The native Decomposition Tree in Power BI does not support fully dynamic hierarchies that change based on slicer selections — the levels must be predefined manually or via field parameters. However, you can simulate the dynamic behavior like this:

Workaround: Use Field Parameters for Dynamic Hierarchy

1. Create a Field Parameter:
   * Go to Modeling → New Parameter → Fields.
   * Select your hierarchy options:
      Division, DSO Region, KPI Customer Cluster, Country Sold To, etc.
   * Name it like: Dynamic Hierarchy.
   
   
2. Use This Parameter in Decomposition Tree:
   

   * Drag Dynamic Hierarchy into the “Explain by” section of the visual.

   * Now users can choose hierarchy levels directly in the slicer.

3. Allow Multiple Selections (Optional):
   

   * Change slicer settings to allow users to choose more than one field.

   * However, only the top selected field will be used at first level — Power BI won't auto-expand deeper.

Suggestions:

Instead of relying fully on the Decomposition Tree:

• Consider a custom visual or Matrix + Drill-through buttons to mimic this behavior more dynamically.
• You may also vote for similar features on ideas.powerbi.com to get it natively supported.

If this workaround helps, please mark as solution and give kudos.

[v-tejrama]

Hi @Mina_PowerBI ,

Thanks for reaching out to the Microsoft fabric community forum.

I've successfully implemented a dynamic Decomposition Tree in Power BI that fully adapts to slicer selections. Instead of a fixed hierarchy, users can choose which fields to drill into such as Customer, Company Code, or DSO Region by leveraging the Field Parameters feature. I created a new parameter from the Modeling tab, selected the required columns, and used the resulting Dynamic Hierarchy table to build a slicer with multi-select enabled. After adding the Decomposition Tree visual, I set my measure (like Total Receivables) in "Analyze" and used Dynamic Hierarchy Fields for "Explain by." This approach gives users complete flexibility to explore the data as needed, fully addressing the original requirement. The only limitation is that Power BI doesn't auto-expand all levels, which is expected, but otherwise the solution works flawlessly.

Please find the attached pbix file for your reference.

Best Regards,
Tejaswi.
Community Support

[Mina_PowerBI]

Hi tejrama, 
Thank you so much for your help! This is helpful but the situation is that it is not auto expanding, and the decomposition tree should be handled by filters based out of columns. Like we contraol bar graphs with filters based on columns. In the same way, I have to move forward with decomposition tree.  And decomposition tree should auto expand. 

[v-tejrama · Accepted solution]

Hello @Mina_PowerBI ,

Hi, thanks for checking in!

I totally get what you're looking for you want the Decomposition Tree to open up automatically when you use slicers or filters, kind of like how bar charts react. Right now, though, Power BI doesn’t let the Decomposition Tree auto-expand.

Even if you set up field parameters or slicers, you still have to click the “+” to drill down each level. That’s just how the visual works at the moment. You can try workarounds like creating separate trees for common drill paths and toggling them with bookmarks, but it’s not fully automated. Some folks have already requested this feature on Power BI Ideas, so it’s worth upvoting if it’s important to you.

For now, manual clicks are still needed to expand the tree.

I'm happy to help if you have any other  queries.

Thank you.

[v-tejrama]

Hi @Mina_PowerBI ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you,

Tejaswi.

[Mina_PowerBI]

Thank you so much  tejrama for so much of your support. I  followed these steps and was able to present dynamic decomposition tree. Thanks a lot!

[Ilgar_Zarbali · Accepted solution]

The native Decomposition Tree in Power BI does not support fully dynamic hierarchies that change based on slicer selections — the levels must be predefined manually or via field parameters. However, you can simulate the dynamic behavior like this:

Workaround: Use Field Parameters for Dynamic Hierarchy

1. Create a Field Parameter:
   * Go to Modeling → New Parameter → Fields.
   * Select your hierarchy options:
      Division, DSO Region, KPI Customer Cluster, Country Sold To, etc.
   * Name it like: Dynamic Hierarchy.
   
   
2. Use This Parameter in Decomposition Tree:
   

   * Drag Dynamic Hierarchy into the “Explain by” section of the visual.

   * Now users can choose hierarchy levels directly in the slicer.

3. Allow Multiple Selections (Optional):
   

   * Change slicer settings to allow users to choose more than one field.

   * However, only the top selected field will be used at first level — Power BI won't auto-expand deeper.

Suggestions:

Instead of relying fully on the Decomposition Tree:

• Consider a custom visual or Matrix + Drill-through buttons to mimic this behavior more dynamically.
• You may also vote for similar features on ideas.powerbi.com to get it natively supported.

If this workaround helps, please mark as solution and give kudos.

[Mina_PowerBI]

Hi ,
Thank you so much for your help! This is helpful but the situation is that it is not auto expanding, and the decomposition tree should be handled by filters based out of columns. Like we contraol bar graphs with filters based on columns. In the same way, I have to move forward with decomposition tree.  And decomposition tree should auto expand.

[johnbasha33 · Accepted solution]

@Mina_PowerBI 

Key Understanding

• The Decomposition Tree visual in Power BI expects static hierarchies.

• But Field Parameters (introduced in 2022) allow dynamic selection of columns as dimensions.

• What cannot be automated directly is:

  • Changing the hierarchy path automatically based on filter selections.

  • Auto-expansion of branches (Power BI still requires user clicks for drill-down).

We can work around this with Field Parameters + slicers.

Step-by-Step Solution

🔧 1. Create a Field Parameter for Dynamic Hierarchy

Go to Modeling > New Parameter > Fields.

Add all 10 dimensions as choices:

Division
DSO Region
KPI Customer Cluster
Country Sold To
Country Ship To
Customer Sold To
Customer Ship To
Customer Payer
Production Plant
Company Code

Rename the parameter as: Dynamic Hierarchy.

This will create:

• A parameter table (Dynamic Hierarchy)

• A DAX column called [Dynamic Hierarchy Fields]

A DAX measure like:

Selected Hierarchy = SELECTEDVALUE('Dynamic Hierarchy'[Dynamic Hierarchy Fields])

Use Dynamic Fields in Decomposition Tree

Add the Dynamic Hierarchy field (from the parameter) into the decomposition tree as your hierarchy level.

Add your measure (like Total Receivables, Count of Customers, etc.) to analyze.

⚠️ Note:

• You can only select one field at a time in decomposition trees.

For multiple levels, the user has to manually expand the hierarchy within the visual, unless you pre-define the field order via slicer control.

Simulate Ordered Path Using a Slicer

To simulate "first this filter, then next," create a multi-select slicer on your Dynamic Hierarchy parameter table.

This allows users to choose the path they want interactively. While Power BI won’t auto-expand based on the slicer, it will enable:

• Controlled inclusion of fields

Dynamic structure driven by user choice

Optional: Create Custom Table for Slicer Order

If needed, create a custom table to track the sequence of hierarchy (using a ranking mechanism):

HierarchyOrder =
DATATABLE(
"Hierarchy", STRING,
"Order", INTEGER,
{
{"Division", 1},
{"DSO Region", 2},
{"KPI Customer Cluster", 3},
...
}
)

Join it to your Dynamic Hierarchy table and sort accordingly. This helps predefine the path in visuals if needed.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Mina_PowerBI]

Hi ,
Thank you so much for your help! This is helpful but the situation is that it is not auto expanding, and the decomposition tree should be handled by filters based out of columns. Dynamic decomposition tree should be user friendly so that it could auto expanding according to what user wants to create hierarchy. Like we contraol bar graphs with filters based on columns. In the same way, I have to move forward with decomposition tree.  And decomposition tree should auto expand.