DATATABLE DAX Function (Table manipulation)
Returns a table with data defined inline.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| name | Repeatable |
A constant string that defines the name of the column. A dynamic expression is not allowed. |
| type | Repeatable |
The data type of the column: INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, DATETIME. |
| data |
The data for the table. Where {values in row1} is a comma delimited set of constant expressions, namely a combination of constants, combined with a handful of basic functions including DATE, TIME, and BLANK, as well as a plus operator between DATE and TIME and a unary minus operator so that negative values can be expressed. The following are all valid values: 3, -5, BLANK(), “2009-04-15 02:45:21”. Values may not refer to anything outside the immediate expression, and cannot refer to columns, tables, relationships, or anything else. A missing value will be treated identically to BLANK(). For example, the following are the same: {1,2,BLANK(),4} {1,2,,4} |
Return values
A table declaring an inline set of values.
Remarks
Unlike DATATABLE, the table constructor allows any scalar expressions as input values.
The syntax used by DATATABLE is different from that used by the table constructor.
The data type name specified in DAX differs from the data types available in the user interface of products that use DAX, such as Power BI, Excel, and Visual Studio. Specifically, CURRENCY corresponds to a Fixed Decimal Number in Power BI, whereas DOUBLE corresponds to the Decimal Number in Power BI and Decimal in the DAX Data types list.
Examples
-- DATATABLE is useful to build constant tables in code.
-- It requires the list of arguments and the list of rows
-- to build the table.
EVALUATE
DATATABLE (
"Name", STRING,
"Ordinal", INTEGER,
{
{ "Small", 1 },
{ "Medium", 2 },
{ "Large", 3 }
}
)
ORDER BY [Ordinal]
| Name | Ordinal |
|---|---|
| Small | 1 |
| Medium | 2 |
| Large | 3 |
Values in the definition of the table cannot be expressions; they need to be constant. The following syntax is not valid and generates an error.
EVALUATE
DATATABLE (
"Aggregation", STRING,
"Value", CURRENCY,
{
{ "Min", MIN ( Sales[Net Price] ) },
{ "Max", MAX ( Sales[Net Price] ) }
}
)
Tables with calculated expressions can be computed using the ROW function, or the table constructor {}, instead of using DATATABLE.
The table constructor requires renaming the columns.
EVALUATE
UNION (
ROW ( "Aggregation", "Min", "Value", MIN ( Sales[Net Price] ) ),
ROW ( "Aggregation", "Max", "Value", MAX ( Sales[Net Price] ) )
)
EVALUATE
SELECTCOLUMNS (
{
( "Min", MIN ( Sales[Net Price] ) ),
( "Max", MAX ( Sales[Net Price] ) )
},
"Aggregation", [Value1],
"Value", [Value2]
)
| Aggregation | Value |
|---|---|
| Min | 0.76 |
| Max | 3,199.99 |
| Aggregation | Value |
|---|---|
| Min | 0.76 |
| Max | 3,199.99 |
Related articles
Learn more about DATATABLE in the following articles:
-
Create Static Tables in DAX Using the DATATABLE Function
You can create static tables in DAX using the DATATABLE function. This article describes the syntax of this new feature and shows when and how to use it. » Read more
-
Understanding data lineage in DAX
Data lineage is such a well-implemented DAX feature that most developers use it without knowing about it. This article describes data lineage and how it can help in producing better DAX code. » Read more
-
Create static tables in Power BI, Power Pivot, and Analysis Services
A quick recap of all the methods available if you need a table with fixed static data in your data model. » Read more
-
Dynamic format strings with calculation groups
This article shows two techniques based on calculation groups: how to implement dynamic format strings in regular measures, and how to perform weight conversion on the fly. » Read more
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/datatable-function