[DAX] TREATAS()

We always want to make our physical relationships from the columns of unique value, or the highest granularity level. In other words, we want to have relationships between table based on the highest granularity level such as DATE. (If DATE is the most granular level.)

However, sometimes our data is not as granular as we want. We may only have the revenue or budget numbers based on year. In this case, how can we create a DAX function and still get the correct number without having to create a physical relationship between tables based on YEAR?

In this situation, TREATAS would come in a very helpful and valuable way.

Syntax

TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]} )

1. Create a virtual relationship between tables. That is, you don’t have to physically set up any relationships between tables in the data model view.
2. See “TREATAS” as a virtual table. Therefore, you can put it in the CALCULATE conditional expression.
3. TREATAS Example =
   CALCULATE( [Total Revenue],
   TREATAS( SUMMARIZE(

Simple TREATAS()

Assumption
We have a Budget Table by Product and by Year as below.

Budget Table by Product and by Year

Goal
To create a table visual to show the yearly budget.

The wrong way
The first solution comes in our mind would be like this:
Total Budget Without TREATAS= SUM(Budget[Sales Budget])

It looks correct, but it shows the wrong answer. Every single year has a budget of $283,544. The reason is that the Budget table is not as granular as we want. Remember, we always want to have the “Date” level granularity if possible.

Wrong Way

The Correct Way
As we discussed earlier, TREATAS() comes in handy when the granularity level between data tables are not the same. (For example, one table is by date, and the other is by year.)

Below shows the formula for with TREATAS.

• With TREATAS, we connect the “Year” column between Calendar Table and Budget Table. That is, TREATAS creates a virtual relationship between the two table.
• Use VALUES() to get the values in the Calendar Year column.

TREATAS Formulas

Below shows the final result with TREATAS. We can see now the budget numbers by year are correct.

Goal — Final Results with TREATAS

TREATAS() & SUMMARIZE()

Goal
To create a table visual to show the yearly budget and by Product

When to use
When the visuals (tables/matrix) come with more columns such as Date & Product.

Below shows the formula for with TREATAS.

• Use SUMMARIZE() to get a virtual table which includes the Product (from Product Data table) and Year (from Calendar table).
• With TREATAS, we connect the SUMMARIZE Product & Year with the Budget Table. That is, TREATAS creates a virtual relationship between the two table.

Below shows the final result with TREATAS. We can see now the budget numbers by Year and by Product are correct.

Goal — Final Results with TREATAS