Sort with Dual()

Creating Calculated Dimensions is a handy way to customize a chart or list box, but it has been difficult to get the sort order correct. Luckily, there is a simple way to get your sort by using the Dual() function. The Dual() function, in its simplest form, brings back two results with one expression. Using it with a Calculated Dimension will let you specify the dimension results along with a numeric value to use as a sort.

A simple example: I need to limit the Regions in a chart to three specific regions. The normal way is to write the following expression:

     = If([Region.Name] = 'CEE', 'CEE',
        If([Region.Name] = 'EUR', ‘EUR',
        If([Region.Name] = 'META', 'META')))

This will bring back only CEE, EUR and META regions.

Using the Dual() function, we add the ability to sort numerically (notice the additions in RED):

     = If([Region.Name] = 'CEE', Dual('CEE',1),
        If([Region.Name] = 'EUR', Dual('EUR',3),
        If([Region.Name] = 'META', Dual('META',2))))

If you use Calculated Dimensions, give this a try!