Creating Reference Tables Using DATATABLE in DAX

What Is DATATABLE in DAX?

DATATABLE is a DAX function that allows you to define a table by specifying its column names, data types, and values directly inside the DAX expression.

It is particularly useful when you need a small, manually defined table for scenarios such as reference values, configuration data, mapping values, thresholds, or business rules.

DATATABLE(
    "Column Name 1", DATA_TYPE,
    "Column Name 2", DATA_TYPE,
    {
        { Value1, Value2 },
        { Value3, Value4 }
    }
)

1. Creating a GST Reference Table

In our example, we want to create a small reference table containing GST percentages, maximum prices, active dates, and status information for different product categories.

Query

// DATATABLE

// Create a Reference Table for GST Values

EVALUATE
    DATATABLE(
        "Category", STRING,
        "GST", INTEGER,
        "MAX PRICE", DOUBLE,
        "ACTIVE DATE", DATETIME,
        "STATUS", BOOLEAN,
        {
            {"Essentials", 0, 10000.5, "2024-02-15 00:00:00", TRUE},
            {"Standard", 18, 100000, "2024-02-15 10:30:45", TRUE},
            {"Luxury", 28, 500000, "2024-02-15 10:30:45", FALSE}
        }
    )

Step-by-step explanation

Step 1: EVALUATE
EVALUATE is used to return the result of a table expression. Since DATATABLE creates a table expression, we use it with EVALUATE to display the resulting table in a DAX query environment.

Step 2: DATATABLE
DATATABLE is the function that defines our table. Inside the function, we specify the table’s columns, their data types, and the rows of data.

Step 3: Define the Category column
The expression “Category”, STRING creates a column named Category and defines its data type as STRING.

Step 4: Define the GST column
The expression “GST”, INTEGER creates a column named GST that stores whole-number values such as 0, 18, and 28.

Step 5: Define the MAX PRICE column
The expression “MAX PRICE”, DOUBLE creates a column for decimal numeric values. This allows values such as 10000.5 to be stored.

Step 6: Define the ACTIVE DATE column
The expression “ACTIVE DATE”, DATETIME creates a column that stores date and time values.

Step 7: Define the STATUS column
The expression “STATUS”, BOOLEAN creates a column that stores logical values such as TRUE and FALSE.

Understanding the column definitions

2. Adding Rows to the DATATABLE

After defining the columns and their data types, we provide the actual rows inside curly braces { }.

{
    {"Essentials", 0, 10000.5, "2024-02-15 00:00:00", TRUE},
    {"Standard", 18, 100000, "2024-02-15 10:30:45", TRUE},
    {"Luxury", 28, 500000, "2024-02-15 10:30:45", FALSE}
}

Step-by-step explanation

Step 1: First row
The first row represents the Essentials category. Its GST is 0, the maximum price is 10000.5, the active date is 2024-02-15 00:00:00, and its status is TRUE.

Step 2: Second row
The second row represents the Standard category. Its GST is 18, the maximum price is 100000, the active date is 2024-02-15 10:30:45, and its status is TRUE.

Step 3: Third row
The third row represents the Luxury category. Its GST is 28, the maximum price is 500000, the active date is 2024-02-15 10:30:45, and its status is FALSE.

3. Understanding the DATATABLE Data Types

One of the important concepts in DATATABLE is that the data type is explicitly defined for every column before the rows are provided.

4. Understanding the Row Structure

Each row inside DATATABLE is represented as a set of values enclosed within curly braces.

{"Essentials", 0, 10000.5, "2024-02-15 00:00:00", TRUE}

The values are mapped to the columns according to their position:

This means the first value belongs to the first column, the second value belongs to the second column, and so on.

5. What Will You Get?

When the query is executed, EVALUATE returns the table created by DATATABLE.

The resulting table contains three rows and five columns, exactly matching the structure and values defined in the DATATABLE expression.

6. Why Use DATATABLE for Reference Data?

DATATABLE can be useful when you have a small set of manually defined values that you want to represent as a table expression.

• Creating small reference tables
• Defining business categories and thresholds
• Storing configuration values
• Creating mapping information for calculations
• Testing DAX logic with a controlled set of values

7. Understanding the Complete Query

Now let’s break down the complete query into its main components.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist