TreatAs is an interesting function with multiple use cases in Power BI. I thought it is good to explain it in a set of articles, the first one here is about what the TreatAs function is and how it can help to build filters and a virtual relationship between two tables. Let’s see how you can use this function.

## Sample Data Model

The sample data model that I use here has three tables with no relationship (Best practice for the below model would be to create the relationship, however, I intentionally removed the relationship to explain the concept to you through TreatAs function);

## Filtering Values Using TREATAS

If I have a visual using EnglishEduction (from DimCustomer table), and SalesAmount (from FactInternetSales table), then it would look like below;

The reason, of course, is not having the relationship between the two tables: DimCustomer and FactInternetSales. I have explained in another article how the relationship acts as a filter in Power BI.

Now, to understand the TreatAs function, let’s see how the structure of function usage is;

*TreatAs(<expression>,<column 1>,<column 2>…)*

The way that you should read TreatAs is this:

Treat <expression> As <column 1>,<column 2>….

Now, there are some rules, in getting TreatAs working

- Expression; the expression part of the function above is a
**table expression**, it means an expression that returns table. That means any function that returns a table can be used here; Summarize, Values, Distinct, …. - Order of Columns in the table expression should be the same as the order of columns in <column 1>,<column 2>…

Considering the two rules above, here is how you can use TreatAs to filter the FactInternetSales table using the value of EnglishEduction in DimCustomer;

SalesAmount using TreatAs = CALCULATE( SUM(FactInternetSales[SalesAmount]), TREATAS(VALUES(DimCustomer[CustomerKey]),FactInternetSales[CustomerKey]) )

The result would be filtered by EnglishEduction, even though there is no relationship between the two tables;

Now, let’s look at the TreatAs expression a bit more closely;

TREATAS(VALUES(DimCustomer[CustomerKey]),FactInternetSales[CustomerKey])

We connected the two tables using the CustomerKey. We are saying that **TREAT** DimCustomer[CustomerKey] **AS** FactInternetSales[CustomerKey]. It means to filter the FactInternetSales[CustomerKey] as of it is DimCustomer[CustomerKey]. Or in other words; If DimCustomer[CustomerKey] is filtered to show only CustomerKey XYZ, then FactInternetSales[CustomerKey] would be also filtered to show only CustomerKey XYZ. It is the same concept of having a relationship, but let’s say a virtual relationship.

The VALUES part of the statement is because the EXPRESSION part should be returning a table not a column. VALUES is returning the unique list of DimCustomer[CustomerKey] column. You can use other options such as below too:

TREATAS(SELECTCOLUMNS(DimCustomer,'CustomerKey',DimCustomer[CustomerKey]),FactInternetSales[CustomerKey])

The expression above will return the same result as Values in the visual mentioned above.

but you cannot just say as below:

TREATAS(DimCustomer[CustomerKey],FactInternetSales[CustomerKey])

This will give you an error that you cannot use a column name in the expression that expects a table expression.

## Using Scalar Values and TREATAS

You will get the most value of TreatAs when you use a derived table using a DAX function such as VALUES or any other function that gets the result of a selection from a table. However, you can even use TREATAS with a scalar table definition. Previously in my article here I explained that you can use a table constructor to build a table in DAX expression.

So it means, I can even have calculations like this:

Customers with High School Eduction = CALCULATE( COUNTROWS(DimCustomer), TREATAS({'High School'},DimCustomer[EnglishEducation]) )

and the result will be count of all customers with their EnglishEducation as High School;

The {“High School”} is a single value table (single row and single column) that filters the DimCustomer[EnglishEduction].

## Virtual Relationship Using TREATAS

The first example that you saw, used TREATAS to filter the SalesAmount in FactInternetSales by the value selected from DimCustomer. You can use this approach to create a virtual relationship. Let’s say you want to do a role-playing-dimension and filter the OrderQuantity using OrderDate and ShipDate both. I have explained how you can use the UseRelationship method or cloning the date table as a role-playing dimension before. here is another way of doing it using TreatAs.

I have a measure for Order Date;

qty by order date = CALCULATE( SUM(FactInternetSales[OrderQuantity]), TREATAS( VALUES(DimDate[DateKey]), FactInternetSales[OrderDateKey] ))

and one for Ship Date;

qty by ship date = CALCULATE( SUM(FactInternetSales[OrderQuantity]), TREATAS( VALUES(DimDate[DateKey]), FactInternetSales[ShipDateKey] ))

And as a result, with no relationship between the DimDate and FactInternetSales I get the calculations working perfectly fine;

Note that I am not saying that it is recommended to implement relationships this way (for both the above examples, it is better to create an actual relationship between tables rather than using TreatAS). All I am showing here to you is the basics of using TreatAs, and how you can virtually create the relationship using this function. In the next article, I’ll show examples of how you can use it to filter based on multiple fields.

## Download Sample Power BI File

Download the sample Power BI report here:

Hi Reza,

thanks for dedicating time to this new intriguing function. Two points:

1) the “Treat This as That” explanation – are you sure? Even from the paragraph that you’ve written “..It means to filter the FactInternetSales[CustomerKey] as of it is DimCustomer[CustomerKey].”,it seems that the order is in revers (treat as that this), meaning the Fact table column is “this” and dimension “that” (the column determining how the other one is treated). Whoa its confusing.

2) Looking forward to more real-world examples where this actually comes in handy

Hi Radek

That might be my English problem, and you might be right in the order.

The bottom line is that the COLUMN(s) will be filtering values based on the result of the EXPRESSION. Would that be called the Treat the Expression As Column? or Treat the Column as Expression? good practice for my English learning for sure 😊

Cheers

Reza

thanks Reza! previously I am bit confused about this func but now it is very clear.

Hi Min

I am glad it helped 😊