TREATAS function in power bi DAX with example and data

The TREATAS function in Power BI is used to apply the result of a table expression as the filter to columns from an unrelated table. It effectively changes the context in which data is evaluated, enabling cross-table calculations without explicit relationships.

Here’s an example to illustrate the TREATAS function with example data:

Example Scenario

Suppose you have two tables:

Sales Data Table:

OrderID	ProductID	SalesAmount
1	101	100
2	102	150
3	101	200
4	103	250
5	104	300

Product Info Table:

ProductID	Category
101	Category A
102	Category B
103	Category A
104	Category B
105	Category C

We want to calculate the total sales amount for each product category using TREATAS.

Steps to Use TREATAS

1. Create Calculated Table: We need a calculated table that maps categories to sales.
2. Use TREATAS Function: We use TREATAS to apply the category filter to the sales data.

DAX Formula Using TREATAS

First, create a new table in Power BI using the following DAX expression:

DAX

TotalSalesByCategory = 
SUMMARIZE (
    ProductInfo, 
    ProductInfo[Category], 
    "TotalSales", 
    CALCULATE (
        SUM(SalesData[SalesAmount]),
        TREATAS (
            VALUES(ProductInfo[ProductID]), 
            SalesData[ProductID]
        )
    )
)

Explanation of the DAX Formula

1. SUMMARIZE Function: This function creates a summary table that groups by the Category column in the ProductInfo table.
2. CALCULATE Function: This function changes the context in which data is evaluated. Here, it calculates the total sales amount.
3. TREATAS Function: This function treats the values from the ProductInfo[ProductID] column as if they were in the SalesData[ProductID] column, enabling filtering based on these values.

Resulting Table

The resulting table SalesByCategory will look like this:

Category	TotalSales
Category A	550
Category B	450
Category C	0

Detailed Breakdown

• Category A:
  • Products 101 and 103 fall under Category A.
  • Total sales: 100 (Order 1) + 200 (Order 3) + 250 (Order 4) = 550.
• Category B:
  • Products 102 and 104 fall under Category B.
  • Total sales: 150 (Order 2) + 300 (Order 5) = 450.
• Category C:
  • Product 105 falls under Category C.
  • Total sales: 0 (No sales for Product 105).

Conclusion

The TREATAS function is powerful for scenarios where you need to apply filters from unrelated tables in Power BI. This example demonstrates how you can use TREATAS to calculate totals across different tables without explicit relationships.ChatG