In this writing, I want to share how it is different when using NATURALINNERJOIN DAX function and TREATAS DAX function in Power BI.
In most cases, when you create and use two virtual tables inside a measure, and if you need to create a virtual relationship between the two virtual tables, and when more than two columns are needed to be connected between the two tables, one of these two DAX functions are mainly used.
Explaining how to use these two DAX functions is out of scope of this writing, however, I will try to describe how those are differently functioning by using a simple sample shown below.
Before I step further, please understand that there are many other ways and easier ways to achieve the final result that is shown here. However, the purpose of this writing is to compare the functionality between the two DAX functions.
Data model
Data table
Rate table
As you can imagine, I want to get a number for each day per country, and per company by calculating [Value] column in the Data table multiplied by [Rate] column in the Rate table. The rate from the Rate table has to be selected correctly by checking matching date, matching country, and matching company from the Rate table that with the Data table.
The relationships, that are created and seen in the model view above, already do most of the jobs and make it easy to write simple DAX in order to achieve the expected result. But, as already mentioned, for the purpose of comparing between the two DAX function, let's start with NATURALINNERJOIN DAX function to create virtual relationship.
Let's go to DAX studio and try to create a virtual table that contains all the information of Data table and one more additional column that contains relevant rate information.
When I tried the above, it shows an error message. Why? The reason is because there are some kind of relationships (Lineage) already existing between the two virtual tables _newDataTable and _newRateTable, even it seems like there are no direct relationship between the two when seeing the model view in the above figure.
Let's try again after breaking the lineage anyway inside the formula.
As seen above, by adding some meaningless expression inside SELECTCOLUMNS DAX formula, it breaks the existing lineage. And it works perfectly.
By using the above virtual table, you can try to see total numbers by writing something like below in order to perform value multiply by a correct-matching-rate (per date, per country, and per company).
The reason why I use SELECTCOLUMNS in here is because I want to make sure that each column name that needs to be connected virtually inside NATURALINNERJOIN DAX function must have the same column name (and the same data type as well). Of course this sample shows the same column name, however, in case you face two tables with different column name, please try using SELECTCOLUMNS DAX function.
Now, let's try TREATAS DAX function.
When seeing the TREATAS function above, it is not easy to understand from the very first glance. However, it shows clearly that it is creating a new virtual relationship without considering the existing relationships. Between Data table and Rate table, there are no existing direct relationship as shown in the data model figure above. And TREATAS creates a virtual relationship between three columns in two tables. Moreover, TREATAS does not require to have the same column name, it just needs to have the same data type in order to be connected properly.
To summarize, I like to describe my opinion like below.
NATURALINNERJOIN: It is easier to understand when creating virtual relationships between two or more than two columns in two virtual tables. But an author needs to consider existing lineage and column names. It means that an author needs to know how to break the existing lineage when needed, and how to re-create column names inside virtual tables if names are not the same.
TREATAS: It is difficult to understand from the first glance. However, it just creates a new relationships, and no need to check column names. Just make sure that the same numbers of columns are used inside the TREATAS function when creating virtual relationships, and columns are in the same order when writing those inside the TREATAS function.
I hope this helps to enjoy using NATURALINNERJOIN and TREATAS functions in Power BI.
Comments