GENERATEALL DAX Function (Table manipulation)
The second table expression will be evaluated for each row in the first table. Returns the crossjoin of the first table with these results, including rows for which the second table expression is empty.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
|
Table1 Iterator |
The base table in Generate. |
|
|
Table2 Row Context |
A table expression that will be evaluated for each row in the first table. |
Return values
Remarks
- If the evaluation of table2 for the current row in table1 returns an empty table, then the current row from table1 will be included in the results and columns corresponding to table2 will have null values for that row. This is different than GENERATE() where the current row from table1 will not be included in the results.
- All column names from table1 and table2 must be different or an error is returned.
» 1 related function
Examples
-- If the second argument returns an empty table, GENERATE skips the row.
-- GENERATEALL returns ALL the rows of the first argument, even
-- though the second expression returns an empty table.
-- GENERATE is similar to CROSS APPLY in SQL
-- GENERATEALL is similar to OUTER APPLY in SQL
DEFINE
VAR Dates =
UNION (
ROW ( "FirstDate", DATE ( 2007, 1, 6 ), "LastDate", DATE ( 2007, 1, 3 ) ),
ROW ( "FirstDate", DATE ( 2007, 1, 9 ), "LastDate", DATE ( 2007, 1, 12 ) )
)
VAR DatesExpanded =
GENERATE (
Dates,
DATESBETWEEN ( 'Date'[Date], [FirstDate], [LastDate] )
)
VAR DatesExpandedAll =
GENERATEALL (
Dates,
DATESBETWEEN ( 'Date'[Date], [FirstDate], [LastDate] )
)
EVALUATE Dates
EVALUATE DatesExpanded
EVALUATE DatesExpandedAll
| FirstDate | LastDate |
|---|---|
| 2007-01-06 | 2007-01-03 |
| 2007-01-09 | 2007-01-12 |
| FirstDate | LastDate | Date |
|---|---|---|
| 2007-01-09 | 2007-01-12 | 2007-01-09 |
| 2007-01-09 | 2007-01-12 | 2007-01-10 |
| 2007-01-09 | 2007-01-12 | 2007-01-11 |
| 2007-01-09 | 2007-01-12 | 2007-01-12 |
| FirstDate | LastDate | Date |
|---|---|---|
| 2007-01-06 | 2007-01-03 | (Blank) |
| 2007-01-09 | 2007-01-12 | 2007-01-09 |
| 2007-01-09 | 2007-01-12 | 2007-01-10 |
| 2007-01-09 | 2007-01-12 | 2007-01-11 |
| 2007-01-09 | 2007-01-12 | 2007-01-12 |
Related articles
Learn more about GENERATEALL in the following articles:
-
Transition Matrix Using Calculated Tables
In the 2015 September update, Power BI introduced calculated tables, which are computed using DAX expressions instead of being loaded from a data source. This article shows the usage of calculated tables to solve the pattern of transition matrix for… » Read more
-
Lookup multiple values in DAX
This article describes different techniques to retrieve multiple values from a lookup table in DAX, improving code readability and performance. » Read more
-
Managing hierarchical organizations in Power BI security roles
This article describes how to apply dynamic security roles in a hierarchical organization to minimize the maintenance effort on the security configuration and obtain the best performance at query time. » Read more
-
Understanding apply semantics for window functions in DAX
This article explains the unique behavior of apply semantics: a new way of computing table expressions when multiple rows are selected in DAX window functions. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/generateall-function-dax