Isolating the worst N members in a set

In the previous recipe, we showed you how to identify members with the highest result. In this recipe, we will do the opposite and return those with the lowest result.

Getting ready

Start SQL Server Management Studio and connect to your SSAS 2016 instance. Click on the New Query button and check that the target database is Adventure Works DW 2016.

In this example, we are going to use the Reseller dimension. Here is the query we will start from:

WITH 
SET [Ordered Resellers] AS 
    Order( [Reseller].[Reseller].[Reseller].MEMBERS, 
           [Measures].[Reseller Sales Amount], 
           BASC ) 
SELECT 
   { [Measures].[Reseller Sales Amount] } ON 0, 
   { [Ordered Resellers] } ON 1 
FROM 
   [Adventure Works] 

Once executed, that query returns reseller sales values for every individual reseller, where the resellers themselves are sorted in ascending order of the sales amount. Our task is to extract only...