DATATABLE DAX Function in Power BI

The DATATABLE DAX function in Power BI is used to create a table with specified columns and data directly within a DAX expression. It’s primarily used for testing, debugging, or creating small, static tables for calculations without relying on external data sources.

DAX Syntax

DATATABLE(
    ColumnName1, DataType1,
    ColumnName2, DataType2,
    ...
    {
        {Row1Value1, Row1Value2, ...},
        {Row2Value1, Row2Value2, ...},
        ...
    }
)     

The function has the following parameters:

• ColumnName: Name of the column (e.g., "ID", "Name").
• DataType: The data type for the column (e.g., INTEGER, DOUBLE, STRING, BOOLEAN, DATETIME, and CURRENCY).
• Row1Value1, Row1Value2, ...: The data rows, where each row is a set of values corresponding to the defined columns.

Note: The start and end value for the sequence can be positive or negative. The incrementValue of the sequence must be non-zero and positive.

Example: Let’s create a calculated table with employee data.

DAX

EmployeeTable = 
DATATABLE(
    "ID", INTEGER,
    "Name", STRING,
    "Salary", DOUBLE,
    {
        {1, "John", 50000},
        {2, "Jane", 60000},
        {3, "Bob", 55000}
    }
)

This creates a table with three columns (ID, Name, Salary) and three rows of data.

Practical Use Case Suppose we need a table for a slicer with predefined ranges:

Example: Let’s create a calculated table with salary ranges.

DAX

SalaryRanges = 
DATATABLE(
    "Range", STRING,
    "Min", INTEGER,
    "Max", INTEGER,
    {
        {"Low", 0, 40000},
        {"Medium", 40001, 60000},
        {"High", 60001, 100000}
    }
)

This table can be used in a slicer to filter data based on salary ranges.