TREATAS – simple calculation using “non existent” relations (DAX – Power Pivot, Power BI)

This is a short explanation of the TREATAS function. It enables us to calculate measures, that usually uses relations, when actually not having them.

It is useful when you can not create a relation.

How to do it?

Let´s say there are two tables. The first one is a dimension, describing names and types of goods.

The second one is a fact table, containing, among other columns, Revenue. This Revenue is going to be grouped by names of goods.

If there was a relation (from Goods ID to Goods ID), it would be a piece of cake. And, actually, no measure would be necessary. But, having no relation, we create a measure containing TREATAS and use it in a visual.

• REVENUE with treatas = CALCULATE(
     SUM(sales_list[Revenue]);
     TREATAS(
        DISTINCT(goods[Goods ID]);
        sales_list[Goods ID]))

Because:

• CALCULATE puts SUM and TREATAS together
• SUM summarizes
• TREATAS defines, which columns from both tables create the “fictional” relation
• DISTINCT (or VALUES) because the relation needs unique values on one side.

Then the result is like this: