Skip to main content

Using NATURALINNERJOIN Function in DAX

This post is about how to perform inner join in DAX. If you don't know what that is you, can access my previous post here which is about join types.

Microsoft's definition for NATURALINNERJOIN is: "Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned." And the syntax is:

NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)  

First of all the definition is wrong. I had two tables with no common column names (check the image below) and I got no error. This definition is really misleading because it makes you think that Power BI creates connection between columns by using common column names but that's not what happens. You need to have a relationship between 2 tables to perform inner join. This Microsoft definition definitely needs correction.


These are my tables and below is the formula with the result table.

As you see there is no error anywhere despite I don't have any common column names. To test my "Power BI needs a relationship, not common column names." idea, I removed the relationship between the tables (column names are different). When I removed it, I got this error message. 

When I changed the column "Name2" to "Name" in Table 2, and delete the relationship, I got the same error. The unexpected thing is, when I create the relationship back (column names are the same) I got another error. If you have the same column names, you get this error.


This is 100% opposite of what Microsoft's definition says. So you should have NOT common column names, otherwise, you get this error. To perform NATURALINNERJOIN successfully, first, you need a relationship between tables. Second, you should NOT have columns with common names. Microsoft's documentation says nothing about relationship and it needs to be added to the documentation. Other than those, columns being joined on must have the same data type in both tables.

To perform inner join on Power Query, we need to use Merge Queries or Merge Queries as new option. At the wizard window which pops up, we should choose inner(only matching rows) as join kind. But at Power Query, we need to have common column names to perform inner join. Otherwise, it gives an error. 



I've seen Microsoft documentation mistakes before, but they were mostly small details. But this one is completely the opposite of what it should be. What I learned during the process is, although it's not the case most of the time, Microsoft's documentations can be very misleading. 

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

Customizing Themes With Advanced Elements in Power BI

Advanced Elements is not an intuitive description, that's why I'm going to show you what I'm talking about directly. For my recent project, I've decided to create my own theme for the report. When I was exploring Customize Theme tab, I have come up with the Advanced section. While text, visuals, page, and filter pane sections are self-explanatory, this advanced section seemed quite enigmatic to me. What are the first-level elements? Or the others? I think Microsoft agrees with me upon being enigmatic because the section comes with "Learn more" option: Click to  Learn More . But when I tried to learn more, it didn't make any sense to me, because I'm a visual person and those descriptions are quite hard to remember. That's why I have decided to be a good samaritan and visualize it, and then share it with you.  As you see, while the border is a third-level element, the shadow is a first-level element. Legend, x-axis, and y-axis are second-level elemen...
Post a Comment
Read more