Chance Insight – Business Intelligence Blog

How to use the Window and OFFSET Functions in DAX Measures:

Written by

CB

in


The Window Function

To use the Window function, we can start by creating a virtual table of customer sales by sales key using the CALCULATETABLE function and then creating a window on the table to return only the results we want by position. We can make use of DAX query to do this.


DEFINE

VAR CustomerSales =  CalculateTable(SUMMARIZECOLUMNS('V_FactOnlineSales'[CustomerKey], "Sales", SUMX('V_FactOnlineSales', V_FactOnlineSales[SalesAmount])), NOT(ISBLANK(V_FactOnlineSales[CustomerKey])))

VAR CustomerSalesWindow = 

    WINDOW(
        1,ABS,3,ABS,
      CustomerSales, 
        ORDERBY([Sales], DESC), 
    )
    
EVALUATE
CustomerSalesWindow


As you can see from the code above, we start off by creating a table in a variable called CustomerSales, which has a column for CustomerKey and a summarized sales column. The table is filtered, so that the customer is not blank.

We then create a second variable called ‘CustomerSalesWindow’ to filter our CustomerSales table using the Window function.
The initial customer sales table is as follows

We then create the window using the following code:


VAR CustomerSalesWindow = 

    WINDOW(
        1,ABS,3,ABS,
      CustomerSales, 
        ORDERBY([Sales], DESC), 
    )

EVALUATE
CustomerSalesWindow


As you can see the original table is sorted by sales in descending order (we can also use partition, but not In this example).

The syntax for the window function is as follows:
WINDOW ( from[, from_type], to[, to_type][, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

So you can see that we have:
WINDOW(
from: 1
from-type: ABS
to: 1
to-type: ABS
relation: CustomerSales
ORDERBY([Sales[, DESC))


As you can see the ORDERBY and PARTITION are very similar to SQL in that it is sorting the sales field in descending order.
The positions from ABS: 1 to ABS: 3 give us the top 3 sales results.

Of course, we could have used something like TOPN() function to get the top 3 sales, but if we wanted a specific location e.g. The 3rd to 5th positions, then the WINDOW() function would make it much easier.

The OFFSET() Function

The OFFSET() function ‘Returns a single row that is positioned either before or after the current row within the same table, by a given offset’.

In this example, we use OFFSET() to display the previous month’s sales in a table. We start off by creating a _SummarySales table and then add a second column _SalesOffest, which uses -1 as the delta which shows the previous month’s sales when the ORDERBY clause on CalanderMonth is set to ascending order (ASC). We can then add the final SalesMonthOnMonth column to the table to get the final summary.

AFDEFINE
	-- Syntax:
	-- OFFSET ( <delta>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )
	
	-- Create Sales by moth table
	
	VAR _SummarySales = SUMMARIZECOLUMNS(
		DimDate[CalendarMonth],
		"Sales", SUMX(
			RELATEDTABLE(FactOnlineSales),
			FactOnlineSales[SalesAmount]
		)
	)

-- Add the offset column by one month

	VAR _SalesOffset =
	ADDCOLUMNS(
		_SummarySales,
		"PreviousMonth", SELECTCOLUMNS(
			OFFSET(
				-1,
				_SummarySales,
				ORDERBY(
					[CalendarMonth],
					ASC
				)
			),
			[Sales]
		)
	)

-- Add a month on month difference column

	VAR _SalesMonthOnMonth =
	ADDCOLUMNS(
		_SalesOffset,
		"Month on Month", [Sales] - [PreviousMonth]
	)

EVALUATE
	_SalesMonthOnMonth

After adding the previous month column, we can then add a month-on-month column.
This gives us a table as follows:



More posts