How and When to Create Virtual Relationships Using the TREATAS Function

[EnterpriseDNA]

In this post, I dive into creating virtual relationships using one of the most advanced DAX functions in Power BI called TREATAS. If you can understand this function, you'll see its many applications across reports and dashboards that you might be creating inside of Power BI desktop.  

TREATAS is a huge help in simplifying your data model. The idea behind using TREATAS is to create virtual relationships between two tables where a physical one cannot be used.

There's a little bit to this, as this is quite advanced. That's why I came up with this post with four detailed tutorials around TREATAS, so that you can get a much better idea of what these virtual relationships are, what they mean, and how you can utilize them inside of Power BI. 

On first look of the TREATAS function, it's difficult to know how and when to use it, and so that’s what the first tutorial video is about. You’ll also see how powerful this function can be when used correctly within Power BI.

[video]

I’ll show you another example on how you can create a virtual relationship within a formula. You’ll see in this tutorial that you don't need to create a physical relationship in your data model. TREATAS enables you to link two tables or columns from two different tables. 

The example that I feature in this demonstration was part of the Enterprise DNA Learning Summit I had around Revenue Budgeting.  

This technique that I'll show you is very crucial and useful when managing budgets versus actual results. You can execute this analysis effectively by combining the power of the model and the correct DAX calculations with the TREATAS function. 

[video]

In this next video, I work through an advanced but real-world analysis involving the TREATAS and other DAX functions. The scenario is when you have products with prices that change over time.  

In Power BI, this is a modeling and formula challenge that needs to be overcome to showcase true historical data and forecast results. The techniques I cover in this tutorial will help you deal with price change and get this working in Power BI. 

Here I used the TREATAS function to create a unique virtual relationship in my data model. There’s a lot of application for TREATAS in forecasting, advertising, and marketing where the product prices don’t always stay the same. You’ll have to use a combination of techniques to solve this type of scenario. 

Check out the video below and see how I did it. 

[video]

The next example on how to create virtual relationships using TREATAS is another demonstration around budgeting and forecasting inside of Power BI. Specifically, in this tutorial, I show you how you can allocate budgets (or forecasts), which might be at a monthly or yearly level, and you need to allocate them to a daily level. 

This is a very common scenario when working with forecasted information. For instance, you may have sales every day, but your budgets are at a monthly granularity. They might be at a quarterly or yearly level, but you want to it break down and have a like for like comparison to the daily results you see in your sales.

To do this inside of Power BI, it requires that you have sufficient knowledge on how to set up the data model and formulate the correct virtual relationships between your tables. Moreover, you also need to use the correct DAX formulas to make this all work in your visualizations. 

Here I cover TREATAS with the CALCULATE and ALL functions, which are key when running this type of analysis. Again, the TREATAS enables the creation of the virtual relationship between tables to accomplish the analysis.  

You’ll see in this video how I did it. 

[video]

Key Take Away 

TREATAS is one of the most interesting DAX functions. It has a very specific usage that can be beneficial if you want to simplify your data model and DAX formulas. It enables the creation of virtual relationships between tables in your data to achieve a certain analysis without having to create a physical table or column. 

The biggest application of the TREATAS function is where data may be at different granularities. Budgeting data is a good example, where it can be at a monthly, quarterly, or yearly level versus your actuals, which are usually at a daily level. 

There are many different data setups and data scenarios where using TREATAS can really simplify things immensely in your model and with the DAX measures that you need to create. If you can find the time to understand how to utilize TREATAS well and combine it with other DAX functions, then you’ll recognize the opportunities available to you in simplifying many different aspects of your own models. 

I hope that you can see how powerful this function is when done correctly in Power BI, and that you’ll be able to implement the techniques I showcase in this post in your own reports and environment. 

Related Links 

Using Power BI DAX Functions To Deal With Products That Have Changing Prices Overtime 

Creating Virtual Relationships In Power BI Using The TREATAS Function 

Allocate Monthly Forecasts Across Daily Results In Power BI Using DAX 

Related Course Modules 

DAX Formula Deep Dives 

Mastering DAX Calculations 

Budgeting & Forecasting 

Related Support Forums 

Budgeting Using TREATAS 

Cumulative Total With A Measure Branched Measure That Utilises TREATAS 

TREATAS - Reconciling The Numbers 

For more TREATAS queries to review see here…..