Power Pivot Principles: The A to Z of DAX Functions – GENERATE

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at GENERATE. 

The GENERATE function

The GENERATE function is one of the table manipulation functions which return a table with the Cartesian product between each row in table1 and the result of evaluating table2 in the context of the current row from table1.  It has the following syntax:

• table1, table2: this is required, and it represents any DAX expression that returns a table.

It should be noted that:

• If table2 is evaluated for the current row in table1 and returns an empty table, the result table will not contain the current row from table1.  This differs from the GENERATEALL function in that the current row from table1 is included in the results, but the columns corresponding to table2 contain null values for that row
• all column names from table1 and table2 must be different or an error is returned
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Let’s consider the following example, where we have these TB_Name_01 and TB_Name_02 tables:

We import these two [2] tables into the Data model and we can write the following DAX in the edit DAX section of a table:

This will create a table that like when we are using CROSSJOIN function to join two [2] tables together:

Let’s say that we have another table called TB_Name_03:

If we want to join multiple tables together using this GENERATE function we will need the nested GENERATE here to do that while using CROSSJOIN function we just need to enter the name of the third table as the third argument:

Both sets of syntax will provide the following Table:

Come back next week 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.