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

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 GENERATEALL.

The GENERATEALL function

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

where:

• table1, table2: these are required and can be any DAX expressions that return a table.

The functions GENERATEALL and GENERATE are very similar and for more information about the latter we recommend our earlier blog article on GENERATE.  It should be noted that:

• if table2 evaluates to an empty table for the current row of table1, the output table of the GENERATEALL function will contain the current row of table1, with null values in the cells corresponding to table2; while the GENERATE function will not include the current row from table1
• all column names from table1and table2 must be different else 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.

We’ve created a short table Dates with two [2] columns Start and End containing date values.

Then we use table Dates as the table1 argument and use the DATESBETWEEN function to generate a table2 argument:

The function DATESBETWEEN returns a single-column table containing dates between Start and End, for each row of table Dates.  Then the GENERATEALL function cross-joins that with the first table Dates:

The above functionality is common between the two [2] functions GENERATEALL and GENERATE.  Now let’s consider another example.  We can change the first table slightly to generate blank values:

Now the second row of the table Dates2 contains an end date earlier than the start date, so that the DATESBETWEEN function will generate an empty table for that row.

The above DAX statement returns the following table, including the blank value:

In contrast, if we employ the GENERATE function:

the blank value will be excluded:

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.