DATATABLE DAX Function (Table manipulation)
Returns a table with data defined inline.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| name | Repeatable |
A column name to be defined. |
| type | Repeatable |
A type name to be associated with the column. |
| data |
The data for the table. |
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.
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
Last update: Nov 23, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/datatable-function