Simplify Data Merging in Power BI with DAX NATURALINNERJOIN

Problem

The need to merge tables when developing Power BI solutions is a fundamental task. In Power BI, DAX offers a powerful function, NATURALINNERJOIN, to streamline this task, making the process of combining data using DAX easier.

In this tip, I will illustrate the workings of the DAX NATURALINNERJOIN function while exploring its syntax and benefits. I will then provide practical code examples to solidify your understanding.

Solution

To create a dynamic table in Power BI using DAX, you would usually follow the steps in the image below to start.

Purpose of the NATURALINNERJOIN Function

The following three points are the most common purpose why the DAX NATURALINNERJOIN function is used in Power BI:

1. It eliminates the need for outright specifying join columns.
2. It combines two tables based on common/shared column names.
3. Like the SQL INNER JOIN function, it retains only rows with matching values in the common columns.

Benefits of using the NATURALINNERJOIN Function?

• Dynamic Analysis: Facilitates table merging on-the-fly based on user selections or other conditions, enabling interactive exploration of data relationships.
• Conciseness: Removes the need for listing join columns, creating more compact and readable expressions.
• Flexibility: With a defined consistency in shared column names, this function would automatically adapt to changes in table structures.

Syntax of the NATURALINNERJOIN Function

The syntax of the DAX NATURALINNERJOIN function has two arguments, as seen in the code below. Both arguments are mandatory, so you must provide both to ensure your DAX code works.

NATURALINNERJOIN(<LeftTable>, <RightTable>)

1. The first argument is the “LeftTable,” which represents the table on the left-hand side or the first table you are joining.
2. The second argument is the “RightTable,” which represents the table on the right-hand side or the second table you are joining.

How Does the NATURALINNERJOIN Function Work?

The following steps highlight how the DAX NATURALINNERJOIN function works:

1. Identifies the columns with identical names in both the LeftTable and the RightTable; and
2. Creates a new dynamic table containing rows with matching values from the common columns.

The table created would contain rows in the LeftTable that match rows in the RightTable, just as a SQL INNER join would. The image below helps to illustrate this further. You can read more about the DAX NATURALINNERJOIN function in its Microsoft documentation.

This tip will use the AdventureWorksDW database tables to show how the DAX function works.

Basic NATURALINNERJOIN Example

In this example, I have used the DimCustomer table as my LeftTable and the FactInternetSales table as my RightTable, as seen in the DAX code image below.

USING NATURALINNERJOIN (With Error) = 
NATURALINNERJOIN (DimCustomer, FactInternetSales)

If you follow the syntax and input the <LeftTable> and <RightTable> parameters as in the DAX code above, it should generate an error message, as seen below.

To resolve the above error, we need to be creative by explicitly specifying the columns we want from both tables and indicating which column is being used as the join column for both tables (in this example, I am using “CustomerKey”).

To achieve the first point, we need to wrap each table and the columns we need to get them into a SELECTCOLUMNS function. Within the SELECTCOLUMNS function, we need to first indicate the join column in quotes. See the modified DAX code below.

USING NATURALINNERJOIN (With Error Message) = 
NATURALINNERJOIN (
    SELECTCOLUMNS (
        DimCustomer,
        "CustomerKey", DimCustomer[FirstName],
        DimCustomer[LastName]
    ),
    SELECTCOLUMNS (
        FactInternetSales,
        "CustomerKey", FactInternetSales[SalesAmount],
        FactInternetSales[OrderDate]
    )
)

The above DAX code would still generate an error, as seen in the image below.

The data type seems to be the issue. So, I will attempt to use the CONVERT function to convert the join columns (“CustomerKey“) from text to Integer, as seen in the DAX code below.

USING NATURALINNERJOIN (With SELECTCOLUMNS) = 
NATURALINNERJOIN (
    SELECTCOLUMNS (
        DimCustomer,
        "CustomerKey", CONVERT ( DimCustomer[CustomerKey], INTEGER ), DimCustomer[FirstName],
        DimCustomer[LastName]
    ),
    SELECTCOLUMNS (
        FactInternetSales,
        "CustomerKey", CONVERT ( FactInternetSales[CustomerKey], INTEGER ), FactInternetSales[SalesAmount],
        FactInternetSales[OrderDate]
    )
)

Now, the NATURALINNERJOIN function is working. See the image below.

If you need to ensure the column names are not prefixed with the original table names, as in the image above, add the column names you need to see in quotes, as in the DAX code and image below.

USING NATURALINNERJOIN (With SELECTCOLUMNS) = 
NATURALINNERJOIN (
    SELECTCOLUMNS (
        DimCustomer,
        "CustomerKey", CONVERT ( DimCustomer[CustomerKey], INTEGER ), "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName]
    ),
    SELECTCOLUMNS (
        FactInternetSales,
        "CustomerKey", CONVERT ( FactInternetSales[CustomerKey], INTEGER ), "SalesAmount", FactInternetSales[SalesAmount],
        "OrderDate", FactInternetSales[OrderDate]
    )
)

Applying Filters Within the NATURALINNERJOIN Example

As you can see in the basic example above, all rows have been returned in the output. But this might not be the desired output. You might want to apply a filter on a column that exists in the DAX code. For example, you may want to return only a merged table of DimCustomer and FactinternetSales using the NATURALINNERJOIN DAX function, but you only need to see where the CustomerKey is “16115”, “24831”, “19706”, and “12436”.

You need to be a little more creative to achieve the output logic above. I have shown an example in the DAX code and image below.

USING NATURALINNERJOIN (With Filter) =
VAR _Join =
    NATURALINNERJOIN (
        SELECTCOLUMNS (
            DimCustomer,
            "CustomerKey", CONVERT ( DimCustomer[CustomerKey], INTEGER ),
            DimCustomer[FirstName],
            DimCustomer[LastName]
        ),
        SELECTCOLUMNS (
           FactInternetSales,
            "CustomerKey", CONVERT ( FactInternetSales[CustomerKey], INTEGER ),
            FactInternetSales[SalesAmount],
            FactInternetSales[OrderDate]
        )
    )
VAR _Filter =
    FILTER ( _Join, [CustomerKey] IN { 16115, 24831, 19706, 12436 } )
RETURN
    _Filter

As shown in the DAX code above, I have leveraged DAX Variables to manage the logic efficiently. Note that I have applied the filter on the “CustomerKey” column, but you can do the same on any column existing in the merged table (i.e., columns merged in the _join variable part).

Using NATURALINNERJOIN in a Measure Example

Using DAX NATURALINNERJOIN in a measure can be handy in various business scenarios where you need to combine data from different tables but maintain simplicity and flexibility. Such scenarios include sales analysis, customer analysis, and inventory management.

Let’s look at one of these scenarios on how we can leverage NATURALINNER JOIN in a measure to solve a business problem related to sales analysis by calculating total sales by customer. We can achieve this by calculating the total sales for each customer by joining the DimCustomer table and the FactInternetSales table on the CustomerKey column, as we have done earlier.

The DAX code below shows how to create a measure using the DAX NATURALINNERJOIN function.

Total Sales (USING NATURALINNERJOIN AS Measure) =
SUMX (
    NATURALINNERJOIN ( DimCustomer, FactInternetSales ),
    FactInternetSales[TotalSales]
)

After you create the measure, you can use it as you would any other measure. Note that I have used SUMX in the measure; you can also use any other aggregate functions as usual.

Important Considerations:

• Data Types: Ensure consistency in data types for joining columns. Columns from both tables must have the same data types.
• Relationships: Take some time to check and establish relationships between the tables in your model.
• Performance: Since you are combining tables, and these tables are evaluated during every query execution, it is important to consider the performance implications this might result in since there is an increase in the size of your model.
• Alternative approach: You can also perform merging (combining) of tables in Power Query. However, you should choose an approach that best suits your model structure and complexity.
• Direct Query Mode: Not supported for use in calculated columns or RLS rules in Direct Query mode. See more information in the Microsoft documentation.

In summary, DAX NATURALINNERJOIN provides a convenient and powerful approach to merging tables in data analysis expressions. By understanding its syntax, benefits, and considerations, you can effectively leverage this function to enhance your data modeling and analysis capabilities within Power BI and other DAX-supported tools. Embrace its simplicity and flexibility to streamline your data merging processes and unlock deeper insights from your data.

Remember to consider the advantages and limitations to choose the most appropriate approach for your data model and desired insights.

Next Steps

• See this YouTube video from SQL BI on using NATURALINNERJOIN in Power BI: NATURALINNERJOIN, NATURALLEFTOUTERJOIN – DAX Guide.
• Read more about considerations of using DAX NATURALINNERJOIN.
• Read more about the DAX NATURALINNERJOIN function from DAX Guide.
• The DAX NATURALINNERJOIN function Microsoft documentation.
• Try this tip out in your own data as business requires.