Rajendra Gupta
Explore decomposition tree

The Decomposition Tree in Power BI Desktop

January 24, 2020 by


Power BI is enriched with many useful visualizations and helps to represent data in various forms. We have explored many use cases of PowerBI in SQLShack articles.

Microsoft regularly provides updates to Power BI desktop and comes up with new enhancements, features. You should always work with the latest version. In this article, I am using the December 2019 Power BI desktop version.

You can check the Power BI desktop from Help->About. Power BI Desktop version

In November Power BI Desktop release, we have a new decomposition tree visual with the capability of artificial intelligence (AI). Yes, it is having inbuilt AI functionality and helps you to drill down the data.

Decomposition Tree Visual in Power BI desktop

We can use the decomposition tree to visualize data in multiple dimensions. We can drill down and analyze data in the hierarchy for a quick analysis. Its’s artificial intelligence (AI) capability enables you to find the next dimension data as per defined criteria. We breakdown (decompose) data into individual categories and determine the high, low values using AI functions in this decomposition visual.

It is a preview feature as of now and does not automatically show in the visualization pane.

Launch Power BI dashboard and click on File -> Options and settings -> Options:

Options and settings

It opens an options page with different configurations. Navigate to the Preview features tab, and you see the checkbox for the Decomposition tree visual feature:

Preview features

Put a check on the Decomposition tree visual feature and click the OK button:

Decomposition tree visual

Once you click the OK button, it pops us a message that Power I Desktop restart will enable this preview feature:

Feature restart required

After the restart, you get the Decomposition tree visualization icon as shown below:

Decomposition tree icon

Click on this visual, and it opens the visual layout. You can adjust the layout for a better visual. It consists of two parts – Visual shape and a bulb that represents artificial intelligence:

Visual fields

Decomposition tree requires two inputs:

  • Analyze: In this input, we specify a column for that you require a breakdown of the tree
  • Explain by: We can add multiple dimensions in this column. We can analyze tree further using these breakdown columns

We will use the AdventureWorksDW2017 sample database for this demonstration. If you do not have this database, visit GitHub and download the backup copy of this database.

Click on the Get Data button in the Home tab and choose the data source as SQL Server:

Get data from SQL Server

It opens a pop-up window, and you can specify the SQL Server instance, database details. We can specify the Data Connectivity mode, either Import or DirectQuery:

Provide SQL Server details

Click the OK button and specify the credentials. We can use either Windows credentials (option – Use my current credentials) or specify alternate credentials:

Credential input

You get warning message of unencrypted connection:

Encryption Support

Click the OK button to ignore this. You get a list of database objects after authentication. Select the appropriate object and load it. In this article, we use vTargetMail view of AdventureWorksDW2017 database:

Sample data

It creates connections and loads data, as shown below:

Data load

Once the data load is complete, we get the fields (output columns) in the field section of Power BI Desktop:

Data fields

Now, we need to drag the fields into Analyze and Explain by columns for the decomposition tree. First, drag the [YearlyIncome] field into the Analyze section. It shows the sum of the [YearlyIncome] into the visual:

Drag the [YearlyIncome] into Analyze section

By default, we get the sum for the column in the Analyze column. We can change it as per requirement such as average, minimum, maximum, variance, median, standard deviation. For this article, we can go with the default option sum:

Different options for data aggregate

The next requirement is to add the fields to Explain by section. We can add multiple fields in this section. Let’s drag few columns – BikeBuyer, MaritialStatus, NumberCarsOwned, YearlyIncome and CommuteDistance and Region.

We can drag the fields in any order. The decomposition chart does not get any influence on the order of columns:

Drag fields into Explain by section

In the above screenshot, we can see that we have selected the fields in explain by column, but it does not show in the decomposition tree visual.

You can notice a plus (+) symbol on the sum of [YearlyIncome] column in the chart:

Expand the decomposition tree

Click on plus (+) icon, and you get columns that we drag into the explain by section. You also get the column order the same as well. If we want to change the order of the columns in which they should appear in the chart, drag those columns in the same order in the explain by section:

Select the require fields for split

Now, from the chart column, click on any column for adding details in the chart. Let’s click on the Region, and it adds regions into the chart.

Add further details into chart

Now, to add further details, click on plus (+) sign on the region and it again populates rest of the fields list:

Add NumberCarsOwned fields into visual

Click on the [NumberCarsOwned], and you get it in the chart. In the below chart, you can see [NumberCarsOwned] for North America:

View different region data

Click on Europe, and you get details of it:

Explore decomposition tree

Similarly, we can add further details to the decomposition tree:

Explore decomposition tree visual

We added further details for [MaritialStatus] in the decomposition tree:

Added further details for [MaritialStatus]

Once you click on any tree path, it highlights that particular path and fades color for others, as shown below:

Traverse a particular path

In case we do not want any particular level in the tree, we can remove it as well. For example, let’s say we do not want [CommuteDistance] section, then click on cross (x) on from the top layer:

Remove a fields from the visual

It removes the field, and the decomposition tree automatically relates to the next level that is [MaritialStatus] in my example:

Decomposition tree after removing a field

Artificial Intelligence decomposition tree split in Power BI Desktop

As highlighted earlier, Microsoft added artificial intelligence (AI) as well in the decomposition tree visual. Once we click on plus (+) symbol, we get a list of columns along with two fields having bulb icon as well as shown below:

Artificial Intelligence visual

Let’s click on the high value and view the visual. It adds a new split [BikeBuyer] along with a bulb icon indicating that it is an AI split:

High Value AI split

You might think why it added this column into the decomposition tree. It gives you an answer for it as well in the visual. Hover your mouse to the bulb icon, and you get the answer:

Reason for the AI split

Our decomposition tree starting point is [YearlyIncome]. Once we use AI split in the tree, it finds the way to find out the highest [YearlyIncome] with available elements.

Now, add another column [Age] into explain by section and remove the [YearlyIncome]. Let’s add another high value (AI) split from the [BikeBuyer] AI column:

Add further AI split in visual

Once we hover mouse on bulb icon for Age, we get the answer of this AI split. [YearlyIncome] is highest when Age is 38:

Check the reason for AI split

Now, remove this Age AI split and click on Low Value field. It automatically checks for the lowest yearly income based on Age:

Low value AI split

It dynamically calculates the value for the selections. For example, once we change the selection to the Europe region, we can see the lowest yearly income when Age is 66:

Check the reason for Low value AI split

Artificial intelligence Analysis type

By default, the Decomposition tree analyzes the AI value mode as Absolute. We can view it from the chart format options:

Analysis type

Let’s change the AI split mode from Absolute to Relative. It changes the AI split in the decomposition tree. You get the answer of the calculation – YearlyIncome is noticeably high when Age is 59:

AI split mode from Absolute to Relative

In the Relative mode, add a new high value AI column, and you see that it adds the Age column as shown below:

Decomposition tree in relative mode AI split

You can see the answer- YearlyIncome is noticeably high when BikeBuyer is 0. You can compare this with the absolute AI mode in which we get the same calculations at first place. You should use the required AI mode as per your requirements. We can add high value and low value in the same decomposition tree. As shown below, we added another low value AI split:

Data split reason

Locking behavior for decomposition tree in Power BI Desktop

You can notice a small lock icon in the report. By default, the lock is opened for all levels:

Locking behavior

What is the lock in the decomposition tree? Power BI Desktop allows the content creator to lock the level so that the user cannot remove it. Suppose we want to lock first two levels of the tree, click on the open lock icon and it changes to lock as shown below:

Lock a field in the visual

Once we lock any level, users do not get any option to remove that level. You do not get a cross (x) icon to remove the level.

We can lock many levels, but suppose we lock level 4; then, it automatically locks the previous level for you.

For example, in the following screenshot, we have all the lock levels open:

Lock further levels

Let’s lock level for [CommuteDistance] and notice that all previous levels locked automatically:

Previous levels automatically locked

Limitations of the decomposition tree in Power BI Desktop

  • We can have a maximum of 50 levels in a decomposition tree
  • It supports maximum 5000 data points at one time in the tree
  • It does not support On-premises Analysis Services, Power BI Report Server, Direct Query, Azure Analysis Services
  • We cannot use features Q&A, Power BI Mobile, Show Data functionality, and Pinning to dashboard with the decomposition tree
  • It is a preview feature while writing this article and it might have few feature changes in the upcoming releases of Power BI Dashboard


In this article, we explored the useful decomposition tree and its useful artificial intelligence (AI) features. It is an excellent visual for breakdown data into certain levels and performs root cause analysis.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

About Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. Personal Blog: https://www.dbblogger.com I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta