In this tutorial, we’ll dive into one of the most advanced DAX functions in Power BI called the TREATAS function. You may watch the full video of this tutorial at the bottom of this blog.
If you can understand TREATAS, you’ll see that it has so many applications for you across all reports and dashboards which you’ll be creating inside Power BI.
TREATAS allows us to simplify our model but it does make our formulas more advanced. That’s the trade-off when using this function.
But still, TREATAS enables us to create virtual relationships between two tables where a physical one cannot be logically used. This means that we don’t have to physically set up any relationships or need any lines to join and compare, for example, the Budget data to the Sales data.
The TREATAS function will do the comparison for us.
There are many applications for this function. The best one is where the data may be at different granularities. One example is budgeting data which may be at a monthly, quarterly, or yearly granularity compared to Actuals which are daily.
A strong understanding of the TREATAS function, and the syntax required, is a better option than reconfiguring the model when it comes to forecasting and budgeting data.
Here’s an example of how this function works.
Using The TREATAS Function
Let us look at examples to see how this function works. I have created two functions here to show you two different ways of setting them up.
Let’s have a look at the first example.
You need to place some columns from one particular table. In this case, it’s the Year.
Then, you need to incorporate some columns from the table or supporting table that has no relationship to the rest of your model. In this case, it’s the Budget Data Year.
The budgeting table is set up like this so that the Budgets for each different Region is only a yearly budget.
This is important because if you use the TREATAS function in the wrong context, you’ll get a meaningless result.
Don’t draw a relationship between the Regions Table or between the Dates Table.
Instead, create the relationship within the formula using the TREATAS function and then place it inside CALCULATE.
Drag the Year and Total Sales to form a table.
Then using TREATAS, get the Budget and allocate it across the Year.
You’ll see that there are no relationships via the model. Instead, you’ve created a relationship between the Year value and the Budget because of the TREATAS function.
The 2016 budget amount is 62 million and the Total Sales amount, derived from the Sales Table, is 60 million. The model doesn’t return a TREATAS result for 2015 because there is nothing to show. The TREATAS function is making sure that there’s no result being returned for 2015.
How To Expand The TREATAS Function
This column shows the same formula using TREATAS.
But you’ll see that it’s giving incorrect results. This is because the context of this calculation is wrong.
There’s the Year 2016, the City, and the Region. However, there’s no physical or virtual relationship being created by the TREATAS function inside the formula for the Region. This is just evaluating to the yearly number of 62 million.
So, this is where you need to expand TREATAS so that you can incorporate more virtual relationships.
This is how to expand it.
Place a summarized virtual table using the SUMMARIZE function in the first part of the TREATAS function. This will enable you to expand the columns you want to compare. It’ll also enable you to build the virtual relationships in between them.
In this case, there’s a virtual table of the City and the Year, and because these two are in the Budget Table, you can then virtually match them up.
You can virtually create multiple relationships, all at once, within this one formula syntax.
Adjusting Columns Within The Table
Under the TREATAS Example 2 column, you’ll see that it’s being broken down by the Year and City.
You can adjust this column by clicking the filter repeatedly until it stops showing any blank entries.
Making Adjustments In The Relationships Table
Now, based on the data, there’s no additional context to be placed because the Budget Table only has a City and a Year.
So, if you wanted to do anything more, you would need to change the budgeting data or add some additional logic to the formula.
For example, there’s only the Budgets at a yearly basis. So, if you want to break up those Budgets for every single day, you’d still be using a very similar logic. But rather than having the relationships doing the filtering, you would have the TREATAS function doing it instead.
Other Ways To Use TREATAS
There are other applications for the TREATAS function other than budgeting analysis.
Let’s have a look at another example.
Looking at the relationships, you can see that the Year is not a column inside the Dates Table at the highest level of granularity.
The Date is the most granular number inside this table.
So, it’s better to not draw physical relationships from the Year. You should always make your relationships from the column containing your unique values. Though, you can still create this dummy relationship virtually using TREATAS.
Conclusion
Hopefully, this tutorial has given you a good idea of the TREATAS function and how you can use it in real-life applications.
There are many different data setups and data scenarios where utilizing TREATAS can really simplify things immensely in your model along with the DAX measures that you need to create.
When you’ve understood how to utilize TREATAS well, you’ll quickly see the opportunities available to you in simplifying many different aspects of your own models.
All the best,
Sam
