Chance Insight – Data Analytics

DAX – Using the Index Function

—

by

CB
in

Demonstrating the use of the Index function with the Contoso Retail Data warehouse. We can start off by building a virtual table in the DAX query editor, which we can use to apply the Index function. The table creates is a list of the first 10 customers by customer key from the DimCustomer table.

DEFINE
	-- Syntax
	-- INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

	VAR CustomerSalesSample =
	CALCULATETABLE(
		SELECTCOLUMNS(
			DimCustomer,
			"CustomerKey", DimCustomer[CustomerKey],
			"FirstName", DimCustomer[FirstName],
			"LastName", DimCustomer[LastName],
			"Total Sales", SUMX(
				RELATEDTABLE(FactOnlineSales),
				FactOnlineSales[SalesAmount]
			)
		),
		TOPN(
			10,
			DimCustomer,
			DimCustomer[CustomerKey],
			ASC
		)
	)

VAR Ind = INDEX(
		1,
		CustomerSalesSample,
		ORDERBY(
			[Total Sales],
			DESC
		)
	)
EVALUATE
	Ind

If we evaluate the CustomerSalesSample table first we can see the table we are working with.

The syntax for the INDEX function is as follows:

— INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )
In our example, we use the <position> of 1 to get the first sales amount, which is the highest sales amount as the Total Sales column is sorted in descending order (DESC)

And then when we evaluate the Index expression (variable Ind), we get the following, which is the correct output we are looking for.

To get the last position, we could either sort the data by ascending order (ASC) or we can use the INDEX of -1 as the following example:

VAR Ind = INDEX(
		-1,
		CustomerSalesSample,
		ORDERBY(
			[Total Sales],
			DESC
		)
	)

When we use -1 as the index we get the blank sales returned, which isn’t what we wanted, so need to modify the code.

One way of filtering out the blanks from the sales table is to add a filter on the table to filter out the blanks as an additional variable as int he below.

DEFINE
	-- Syntax
	-- INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

	VAR CustomerSalesSample =
	CALCULATETABLE(
		SELECTCOLUMNS(
			DimCustomer,
			"CustomerKey", DimCustomer[CustomerKey],
			"FirstName", DimCustomer[FirstName],
			"LastName", DimCustomer[LastName],
			"Total Sales", SUMX(
				RELATEDTABLE(FactOnlineSales),
				FactOnlineSales[SalesAmount]
			)
		),
		TOPN(
			10,
			DimCustomer,
			DimCustomer[CustomerKey],
			ASC
		) 

	)
	VAR CustomerSalesNotBlank = FILTER(CustomerSalesSample, NOT(ISBLANK([Total Sales])))	

	VAR Ind = INDEX(
		-1,
		CustomerSalesNotBlank,
		ORDERBY(
			[Total Sales],
			DESC
		)
	)
EVALUATE
 Ind

Evaluating this code now gives us the least total sales amount that is not blank: