Power Pivot Principles: Introducing the Function INTERSECT

Welcome back to the Power Pivot Principles blog.  This week, we are going to learn one way of joining tables together in DAX.

The SQL language offers different types of JOIN such as INNER JOIN and OUTER JOIN etc.  Similar to the SQL language, DAX offers some expressions producing a result equivalent to certain types of JOIN.  This week, we are going to learn a new function to replicate the JOIN effect in DAX.

INTERSECT returns the row intersection of two tables, retaining duplicates.  It has following syntax to operate:

where:

• <table_expression1> and <table_expression2> are any expressions that result in a table.

Consider the data tables EmployeeTable1 and EmployeeTable2:

The data tables contain the information for employee name and department.  If we want to perform the inner join (i.e. find matching rows) on table EmployeeTable2 by filtering the row context we use the measure:

The function INTERSECT here evaluates the two table expressions and returns a table containing all of the rows in EmployeeTable2 that are also EmployeeTable1 (i.e. there are matching rows).  We use the method of evaluation introduced in my previous blog to return the result table in the workbook. 

So far, we have pretty much always used the ‘New Measure’ dialog.  Let’s do it another way this week.  If I were to right-click on the second Table and select Table -> Edit DAX… from the shortcut menus , we can create the syntax as follows:

The result would be:

The result returns row for records with employee name B and C. If we choose to evaluate the result based on single column, the result would return error. For example, if we rewrite the measure as:

There would be error message indicating that the query could not be assessed since a single value is used in this case, not a table expression.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.