Skip to main content

NATURALLEFTOUTERJOIN Function in DAX

This post is about how to perform left join and right join in DAX, and in Power Query. If you need to refresh your memory about join types, you can read my post about it here. 

NATURALLEFTOUTERJOIN is used to perform left join in DAX. Left join means combining two tables by taking all the rows from left table and only the matching ones from right table. Microsoft's definition for this function is exactly the same as their definition for NATURALINNERJOIN meaning somebody made a wrong copy-paste. Here comes their links:

https://docs.microsoft.com/en-us/dax/naturalleftouterjoin-function-dax

https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax

The syntax is pretty simple:

NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>)

Like I always do, I am going to explain it over an example. These are my tables that I'm planning the join. 


This is the formula to join them and the error message comes with it.

Like any other join function in DAX, it requires for columns to have different names to function. After changing the first column's name in Table B from 'Name' to 'Name2', my formula works. It gets all rows from the left table and only the matching ones from the second table like it supposed to do.


For experimental purposes, I have moved the 'Name2' column to the end of the table and I got the same result again. It means the order of columns is irrelevant to the output. 

If you look for a function to perform right join in DAX, you can't find any because there is none. Is that mean we can't perform right join in DAX? The answer is no. We can perform right join in DAX by using NATURALLEFTOUTERJOIN function. What a mouthful name! Anyway, we can perform right join with the same function only by changing the order of tables inside the formula. Power BI takes the whole table from the first parameter and only the matching ones from the second parameter. It looks like other than being mouthful, it also doesn't reflect the whole capacity of the function. The formula below shows how we can perform right join by using the same formula.

All I did was putting the right table to the first parameter and left table to the second. That change was enough to perform right join with the same formula.

To perform right join or left join using Power Query we need to use Merge Queries or Merge Queries as New options. After that, we can choose Left Outer or Right Outer from the menu.

The difference is when we are joining tables in Power Query, having common column names doesn't create any problem. Lastly, the order of the columns is irrelevant to the output like it is in DAX.
Labels:

Comments

Post a Comment

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...
Post a Comment
Read more

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I  have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website  base64-image.de  and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...
Post a Comment
Read more

Solving Data Type Conflicts in Power BI and SQL

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about. The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print  "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: ...
Post a Comment
Read more