GENERATE 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.
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 not be included in the results. This is different than GENERATEALL() where the current row from table1 will be included in the results and columns corresponding to table2 will have null values for that row.
- All column names from table1 and table2 must be different or an error is returned.
» 1 related function
Examples
-- GENERATE is an iterator: the second argument is evaluated in a row context DEFINE VAR Dates = UNION ( ROW ( "FirstDate", DATE ( 2007, 1, 1 ), "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] ) ) EVALUATE Dates EVALUATE DatesExpanded
FirstDate | LastDate |
---|---|
2007-01-01 | 2007-01-03 |
2007-01-09 | 2007-01-12 |
FirstDate | LastDate | Date |
---|---|---|
2007-01-01 | 2007-01-03 | 2007-01-01 |
2007-01-01 | 2007-01-03 | 2007-01-02 |
2007-01-01 | 2007-01-03 | 2007-01-03 |
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 |
-- 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 GENERATE in the following articles:
-
Using GENERATE and ROW instead of ADDCOLUMNS in DAX
This article explains how to improve DAX queries using GENERATE and ROW instead of ADDCOLUMNS when you create table expressions. » Read more
-
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
-
Filtering the Top 3 products for each category in Power BI
This article describes different techniques to display the first three products for each category in Power BI. It includes considerations on how to adapt the technique to different models and requirements. » Read more
-
Using join functions in DAX
This article describes the practical uses of NATURALLEFTOUTERJOIN and NATURALINNERJOIN in DAX. These functions are not commonly used in DAX because they do not have the same flexibility as the corresponding concepts in SQL. » Read more
-
Preparing a data model for Sankey Charts in Power BI
This article describes how to correctly shape a data model and prepare data to use a Sankey Chart as a funnel, considering events related to a customer (contact, trial, subscription, renewal, and others). » Read more
Related functions
Other related functions are:
Last update: Oct 3, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber,
Microsoft documentation: https://docs.microsoft.com/en-us/dax/generate-function-dax