TREATAS

How to Use the Power BI DAX function TREATAS

What is TREATAS?

TREATAS is a DAX function that allows us to create a virtual table that we can use to filter other tables. The virtual table created by TREATAS can be based on a combination of columns from one or more tables. Once created, we can use this virtual table to filter other tables using the values from the virtual table.

Syntax

The syntax for TREATAS is as follows:

TREATAS(❰expression❱, ❰table❱[,❰table❱…])

The expression parameter is used to define the values that we want to use as filters. The table parameter is used to specify the table or tables that we want to apply the filters to.

How to Use TREATAS

To illustrate how TREATAS works, let's consider an example. Suppose we have two tables in our Power BI report: Sales and Budget. The Sales table contains information about sales made by our company, while the Budget table contains information about the budget allocated for each department.

Now, let's say we want to create a new measure that calculates the variance between actual sales and budgeted sales for each department. To do this, we need to use the TREATAS function to filter the Sales table using the Department column from the Budget table.

Here's how we can create this measure:

1. Create a new measure by going to the Modeling tab and clicking on New Measure.

2. In the formula bar, enter the following formula:

Variance = SUM(Sales[Sales Amount]) – CALCULATE(SUM(Budget[Budget Amount]), TREATAS(VALUES(Budget[Department]), Sales[Department]))

Let's break down this formula:

- SUM(Sales[Sales Amount]): This calculates the total sales amount for all departments.

- SUM(Budget[Budget Amount]): This calculates the total budget amount for all departments.

- CALCULATE(SUM(Budget[Budget Amount]), TREATAS(VALUES(Budget[Department]), Sales[Department])): This calculates the total budget amount for each department, filtered by the Department column in the Sales table using the TREATAS function.

3. Click on the check mark to save the measure.

Now, we can use this measure in our report to create a visual that shows the variance between the actual sales and budgeted sales for each department.

The TREATAS function in Power BI is a powerful tool that can be used to perform complex filtering on multiple tables at once. By creating a virtual table and using it to filter other tables, we can create more sophisticated measures and visuals in our reports. With the syntax and examples provided in this article, you should be well-equipped to start using the TREATAS function in your own Power BI reports.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)