Dax Function: DATATABLE
Category: Table Manipulation Functions
The DATATABLE function in Power BI is a DAX function used to create a custom, static table directly within a Power BI data model. This function enables users to define table structure and content without importing data from external sources.
Purpose
To generate static tables for use in calculations, visuals, and reports.
Useful for creating reference tables, lookup tables, or quick prototypes.
Eliminates the need for external data sources for small tables.
Type of Calculations
Creates static, in-memory tables.
Does not support dynamic or calculated values; all values must be explicitly defined.
Practical Use Cases
Lookup Tables: Create mapping or reference tables for DAX calculations.
Static Data Representation: Quickly prototype tables for testing measures or relationships.
Parameter Tables: Define parameters or settings for slicers and filters.
DATATABLE (
ColumnName1, DataType1, ColumnName2, DataType2, ...,
{
{ Value1, Value2, ... },
{ Value3, Value4, ... },
...
}
)
| Parameter | Type | Description |
|---|---|---|
| ColumnName1, … | String | Names of the columns in the table. Must be unique. |
| DataType1, … | String | Specifies the data type of each column. Supported types include STRING, INTEGER, DOUBLE, and BOOLEAN. |
| { Values } | Array | Defines the rows of data for the table. Each row must match the column structure. |
How Does DATATABLE Dax Works
Define Columns: Specify column names and data types.
Insert Data: Provide rows of data as an array. Each row must align with the column structure.
Create Table: Power BI processes the defined schema and generates an in-memory static table.
Key Points
The table is static; it does not update dynamically based on changes in the dataset.
The data types specified must align with Power BI’s accepted DAX data types.
What Does It Return?
Table: A static table with the specified structure and data.
When Should We Use It?
For Reference Tables: When small, static lookup tables are required.
Quick Prototyping: For creating tables without importing external files.
Parameter Tables: When slicers or filters depend on predefined options.
Examples
Basic Usage :
Create a static table of product categories:
DATATABLE (
"Category", STRING,
"ID", INTEGER,
{
{"Electronics", 1},
{"Clothing", 2},
{"Groceries", 3}
}
)
Result: A table with columns Category and ID containing the specified rows.
Column Usage
Define a table with boolean and numeric data:
DATATABLE (
"Item", STRING,
"In Stock", BOOLEAN,
"Price", DOUBLE,
{
{"Laptop", TRUE, 999.99},
{"Shirt", FALSE, 29.99}
}
)
Result: A table with columns for Item, In Stock, and Price.
Advanced Usage
Use in combination with a slicer:
DATATABLE (
"Parameter", STRING,
"Value", INTEGER,
{
{"Low", 1},
{"Medium", 2},
{"High", 3}
}
)
Usage: Link this table to a slicer to allow users to select a parameter level for calculations.
Tips and Tricks
Use for small, static datasets only. For larger or dynamic datasets, import data from a source.
Ensure column names are unique and follow consistent naming conventions.
Data Mismatch: Rows must align with the defined column structure.
Lack of Dynamism: Changes in external data sources will not reflect in the DATATABLE function.
Performance Impact of DATATABLE DAX Function:
Efficient for small tables but may consume memory unnecessarily for large datasets.
Avoid using for dynamic or frequently updated data; these are better handled by imported tables or calculated tables.
Related Functions You Might Need
| Function | Description |
|---|---|
GROUPBY | Groups a table by one or more columns, supporting advanced aggregations. |
SUMMARIZECOLUMNS | Creates a summary table with grouping and aggregations. |
FILTER | Filters a table based on a condition. |
SUMX | Iterates over a table, performing calculations for each row. |
Want to Learn More?
For more information, check out the official Microsoft documentation for DATATABLE You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
The DATATABLE function allows you to create a static table directly within your Power BI model, without relying on external data sources.
No, all data in a DATATABLE must be explicitly defined. It does not support calculated columns or dynamic values.
Use DATATABLE for small, static datasets like reference tables or quick prototypes where importing external data isn’t necessary.
Supported data types include STRING, INTEGER, DOUBLE, and BOOLEAN.
No, DATATABLE is static and does not reflect changes in external data or calculations.