Creating a RANKX() calculated column which obeys filter context

Question

I am doing some analysis of our customers and I have an existing column which 'ranks' the number of orders each customer has placed by date ordered. By this I mean if a customer has placed 4 orders on 4 different dates the 'PersistantOrderCount' column that I have will show as follows:

OrderID | OrderDate | CustomerID | PersistantOrderCount

101 | 01/01/2015 | 1 | 1

102 | 01/02/2012 | 1 | 2

103 | 01/03/2015 | 1 | 3

104 | 01/04/2015 | 2 | 1

105 | 01/05/2015 | 3 | 1

106 | 01/06/2015 | 1 | 4

As you can hopefully see in this example the customers orders are ranked based on the order date with their first order being 1 and, in the case of customer '1', their last order being 4.

The formula I use for this is:

=
RANKX (
    FILTER (
        'DataSet',
        [CustomerID] = EARLIER ( [CustomerID] )
    ),
    [OrderID],
    [OrderID],
    1,
    DENSE
)

The ranking of orders does not change using this formula based on any filters I have selected.

What I need now is a similar column but where the ranking is based on the filters I have applied, in particular I need to be able to filter by date. If for example I use exactly the same data but have it filtered only for months Mar, Apr, May, Jun I would want to see the following:

OrderID | OrderDate | CustomerID | PersistantOrderCount

103 | 01/03/2015 | 1 | 1

104 | 01/04/2015 | 2 | 1

105 | 01/05/2015 | 3 | 1

106 | 01/06/2015 | 1 | 2

Note how order 103 and 106, which were previously ranked 3 and 4 respectively, are now ranked 1 and 2 because within the selected date period they were the customers first and second order.

Can anyone help me do this?

I have tried reorganising my formula to use CALCULATE() with the idea of using its context sensitive filters but this did not work. This has been very frustrating for me so any help is appreciated.

Thanks.

Gordon K Gordon K · Accepted Answer · 2015-07-15T08:21:41

Rather than doing this by adding a column, my approach would be to add a measure for each of the fields that you need:

[PersistantOrderCount]:=RANKX(FILTER(ALL('Dataset'),'Dataset'[CustomerID]=MAX('Dataset'[CustomerID])),FIRSTDATE('Dataset'[OrderDate]),,1,DENSE)

and

[FilteredOrderCount]:=RANKX(FILTER(ALLSELECTED('Dataset'),'Dataset'[CustomerID]=MAX('Dataset'[CustomerID])),FIRSTDATE('Dataset'[OrderDate]),,1,DENSE)

Which gives you the following result for an unfiltered dataset: enter image description here

And this result when you select months March, April, May and June: enter image description here

Creating a RANKX() calculated column which obeys filter context

1 Answers