Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Special holiday offer! You and a friend can attend FabCon with a BOGO code. Supplies are limited. Register now.

SachinNandanwar
Super User
4 hours ago

ABS & REL parameters in DAX Window function

The WINDOW function in DAX has made life easier when dealing with complex calculations—especially those that require running totals, ranking, and dynamic windowed aggregations across different contexts and partitions.

 

I am particularly intrigued by the ABS and REL arguments of the WINDOW function, as they provide a high level of flexibility when working with running totals across window partitions. In this post, I will share some insights on how to leverage these two arguments effectively.

Definition

ABS: Starts the window from the first row of the partition (absolute position).

REL: Ends the window at a position relative to the current row.

 

You can find the official definition here

 

Sounds simple, right? Maybe. Let’s look at a few examples using the WINDOW function.

 

We have a dataset with the following columns: Region_id, Region, Sale Date, Total Sales

 

SachinNandanwar_0-1766075574070.png

With the new WINDOW function, calculating a running total has become easier across window partitions. The partition for this data is based on Region.

Window_Fn =
CALCULATE (
    SUM ( Table[total_sales] ),
    WINDOW (
        0,
        ABS,
        1,
        REL,
        ALLEXCEPT ( Table, Table[region] ),
        ORDERBY ( Table[Date].[Date], ASC ),
        PARTITIONBY ( Table[region_id] )
    )
)

The above measure Window_Fn with 0, ABS and 1, REL implies that for the current row, the aggregateSUM(total_sales) is calculated as:

  • All previous rows in the partition
  • The current row
  • The immediate next row relative to the current row

Let’s focus on the output for North America and Europe.

 

SachinNandanwar_0-1766076379373.png

 

For North America for the date 1/1/2024 the calculated window value would be 1500+1550=3050.This is because the 0th(which defaults to 1) absolute position of the partition is 1/1/2024 and the relative position which is next row relative to the current row of the partition is 1/5/2024.Now this would repeat for each row till we reach a position in the partition where the REL value and the ABS value for a given current row are equal. This happens for row where the Sale Date is 1/20/2024 and 1/25/2024 for North America and 1/23/2024 and 1/28/2024 for Europe.

 

At row 1/20/2024 the relative row is 1/25/2024 and for 1/25/2024 for North America there is no relative row value , so the value of the measure will be sum of all the total_sales values in the partition for those 2 rows.

 

This continues until the ABS and REL boundaries converg for example at 1/20/2024 and 1/25/2024 for North America, and 1/23/2024 and 1/28/2024 for Europe.

 

Lets change the ABS and REL values to 2 and -1 respectively. In this case it tells the window to start at second row of the partition and for any given current row the partition ends one row prior to the current row.

 

Window_Fn =
CALCULATE (
    SUM ( Table[total_sales] ),
    WINDOW (
        2,
        ABS,
        -1,
        REL,
        ALLEXCEPT ( Table, Table[region] ),
        ORDERBY ( Table[Date].[Date], ASC ),
        PARTITIONBY ( Table[region_id] )
    )
)

SachinNandanwar_0-1766076741075.png

 

Here, the window starts at the second row of the partition and ends one row prior to the current row. If the relative boundary is before the absolute boundary, the result is blank.

 

The ABS value of the partition starts at 1/5/2024. For the first row of the partition for North America the ABS and REL value wouldn’t add much meaning as there is no row prior to 1/1/2024 and the row that matches the ABS value of 2 is 1/5/2024.

 

To put it more into perspective, say for example if there's an interval with a start value of 10 and an end value of 2, it’s impractical to find a value of 3 within that range. This makes the proposition illogical or meaningless. Same would be the case for row 1/5/2024 for North America and 1/8/2024 for Europe. Remember the start value of the partition is 1/5/2024 for North America and the relative value for each current row should be equal or greater than the absolute value.

 

Lets move to the third row i.e. 1/10/2024 for North America. For this row the window starts at 1/5/2024 and ends at 1/5/2024 as the ABS value is 1/5/2024 and the relative value for the current row of 1/10/2024 is 1/5/2024.

 

Lets perform the same checks for rows of Europe. The start date of the partition is 1/8/2024 and for the first row 1/3/2024 the relative value does not exist. Moving to the next row 1/8/2024 the start row would be 1/8/2024 and end row is 1/3/2024 which is meaningless. For the third row 1/13/2024 the start of the window is 1/8/2024 and end of the window is 1/8/2024.

 

For the last row for Europe, the window partition starts at 1/8/2024 and ends at 1/23/2024.Output of the function for this row would the sum of all values of Total Sales from date 1/8/2024 to 1/23/2024 i.e. 1250+1300+1350+1400 =5300.

In the last two previous examples we saw the start position of the partition defined by ABS and the end partition by REL.

 

Let us see in this example on how the summation is calculated where the start position of the partition is defined with REL and the end position by ABS.

Let us now reverse the logic—using REL to define the start and ABS to define the end.

Window_Fn =
CALCULATE (
    SUM ( Table[total_sales] ),
    WINDOW (
        -2,
        REL,
        2,
        ABS,
        ALLEXCEPT ( Table, Table[region] ),
        ORDERBY ( Table[Date].[Date], ASC ),
        PARTITIONBY ( Table[region_id] )
    )
)

 

SachinNandanwar_0-1766076882880.png

 

The start row of the partition for North America for any given current row is two rows before the current row and the end row is the second row of the partition. So, for the first row in the partition the output for the window function is 1500+1550 as there are no rows prior to 1/1/2024.For second row as well it would be the same 1550+1500 and same would be the case for third row.

 

For the fourth row, as the argument is two rows relative, the start row would be 1/5/2024 which is the equal to the start row of the partition defined by ABS , thus the summation will be 1550 .The two rows relative for the current row of 1/15/2024 is 1/5/2024 and the absolute value is also is 1/5/2024 the output for this row of 1/15/2024 is 1550.

 

The output of the measure for the fifth row will be empty as the start row for the partition is in between 1/10/2024 and 1/5/2024 which is meaningless. Same is the case for the sixth and final row 1/25/20204.

To sum it up :

  • The window starts two rows before the current row
  • The window ends at the second row of the partition
  • If the window boundaries are invalid, the measure returns blank

For North America:

  • First few rows include all available rows up to the ABS boundary
  • Later rows return blank when the window becomes illogical

Conclusion

The WINDOW function significantly simplifies complex analytical calculations in DAX. A solid understanding of the ABS and REL parameters provides powerful flexibility for performing row-by-row calculations across window partitions.

Labels:
0