Learn How to Filter Data Across Unrelated Tables with TREATAS in Power BI DAX

In this blog post, I’m going to show you how to use the TREATAS function in DAX to filter data across tables without creating relationships. This is a very powerful and useful technique that can help you create dynamic and flexible reports with ease.

TREATAS is a function that takes a table expression as the first argument and one or more columns as the second argument. It then applies the filter context of the table expression to the columns, as if they were related by a one-to-many or many-to-many relationship. This way, you can filter data across tables without having to create explicit relationships in the model.

TREATAS (
VALUES ( <lookup_column> ),
<target_column>
)

Let me give you an example of how TREATAS works. Suppose you have two tables: Sales and Product. Sales table contains information about the sales transactions, such as date, quantity, and productkey. Product table contains information about the products, such as name, category, and price. There is no relationship between these two tables in the model.

Now, suppose you want to create a report that shows the total sales amount by product category. You can use TREATAS to apply the filter context of the Product table to the Sales table by using the following formula:

SalesAmount with TREATAS =
CALCULATE (
SUM ( Sales[Sales Amount] ),
TREATAS ( VALUES ( 'Product'[ProductKey] ), Sales[ProductKey] )
)

This formula will return the total sales amount for each product category, as if there was a relationship between Sales[ProductKey] and Products[ProductKey]. You can then use this measure in a visual, such as a table, to see the results.

TREATAS is a very versatile function that can help you filter data across tables in many scenarios.

If you enjoyed this blog post and learned something useful and would like to receive notifications about my new blogs, please follow and subscribe to my Medium account. Also, please don’t forget to give me a clap and save it on your reading list. Stay tuned for my next blog posts with tons of other Power BI tips and tricks. Feel free to leave your comments and feedback below. Thank you for visiting!