I’m excited to share the news with you that we have added a new feature in Power BI Helper; Expression Tree. Expression Tree will expand the tree of expression for a Measure or calculated column, you can see what other measures are used to create this expression, and where other measures, calculated columns, or even normal columns are located (in which table). This feature is in addition to previous two features of this tool which were; Showing tables and fields used in visualization pages of a Power BI Report, and ability to search for a column or table that used in visualization pages of a report. In this post, I’ll explain how this new feature works.
Defining the Problem
You have a Power BI with many measures that are referenced each other, it is not easy to find out which measure is used to create which calculation. If you want to go through the list of all measures, columns, and calculated columns used in a measure it may take hours to find it. Here is an example of a measure’s code;
supermeasure = [SumSales]+[SumProfit]-SUM(FactInternetSales[Profit])
This measure is sourced from two other measures and also a column in a table. Finding the column was easy, however, finding the description of other measures are not, you need to go to Power BI Desktop, select the other measures one by one and see the expression for those, and it is very likely that those measures also have used other measures inside their expressions;
SumProfit = [SumSales] - SUM(FactInternetSales[TotalProductCost])
In real-world scenarios of using Power BI and DAX, you end up with a big list of measures and expressions. Finding the expression tree is always a challenge!
Let’s now have a look at this functionality in Power BI Helper.
Connect to the Power BI Model and Get List of Tables, Columns, and Measures
For this version of Power BI Helper, you need to have Power BI Desktop file opened on your machine, when it is opened, you can connect to that model;
After connecting to the model, you will see list of all tables in the model and all measures;
With selecting every table, you can see list of all columns under that table too;
View the Measure’s Expression Tree and Expression Line
If you click on any measure in the list of measures, the expression of that will be showed in the Measure Expression section, and the Measure Dependency Tree will be showed in a tree-style visualization;
Now with this expression tree, you can easily learn what are measures, calculated columns, or columns from tables are participated in creating this measure;
Other Features of Power BI Helper
If you want to learn about other features of Power BI Helper, read posts below;
This is the version 0.3 of Power BI Helper, and still, long list of upcoming features exists. The feature of the expression tree is still under test, it will help developers to find the tree of expression dependency easier. It will help new developers in your Power BI project to get along with your model and report faster, rather than spending lots of time and money to get familiar with it. Let us know if you have any suggestions for upcoming features.
We need your help
If you like to help us, please test this with your Power BI files and let us know the result, this tool is under test, and any help from you is the most welcome 🙂