Daily DAX : Day 325 RANGE

Power BI DAX RANGE Function

Overview

The RANGE function in DAX (Data Analysis Expressions) is used in Power BI to generate a table with a sequence of numbers within a specified range. It is particularly useful for creating dynamic tables or lists of values for calculations, iterations, or visualizations.

Syntax

RANGE(<start>, <end>, [<step>])

• start: The starting number of the sequence.
• end: The ending number of the sequence (inclusive).
• step (optional): The increment between numbers in the sequence. Default is 1.

Return Value

A single-column table named Value containing the sequence of numbers from start to end, incremented by step.

Use Case

The RANGE function is often used in scenarios where you need to:

• Create a series of dates or numbers for a custom calendar or index.
• Generate iterative calculations, such as running totals or simulations.
• Fill gaps in data for visualizations or calculations.
• Support dynamic filtering or parameter tables.

Example

Suppose you want to create a table with numbers from 1 to 10 to use as an index for calculations.

IndexTable = RANGE(1, 10, 1)

Result: A table with a single column Value containing numbers 1, 2, 3, ..., 10.

Practical Example

Create a sequence of even numbers from 2 to 20 for a report:

EvenNumbers = RANGE(2, 20, 2)

Result: A table with values 2, 4, 6, ..., 20.

Notes

• The step parameter must be positive. Negative steps are not supported.
• If start is greater than end, an empty table is returned.
• Use with other DAX functions like ADDCOLUMNS or GENERATE to extend functionality.

Common Use Case: Date Table

Combine RANGE with DATEADD to create a dynamic date table:

DateTable = 
VAR StartDate = DATE(2023, 1, 1)
RETURN
ADDCOLUMNS(
    RANGE(0, 364, 1),
    "Date", StartDate + [Value]
)

Result: A table with 365 dates starting from January 1, 2023.