Ranking Subsets of a table

Question

I've tried very hard to work this out for myself but failed miserably.  I have a transaction table with User ID, Transaction Type (add, redeem, spend) and Date.  I want to rank, for each user, the transaction types by date i.e. 1st Add, 2nd Add. In principal I understand I need to filter the table to transaction type e.g. "add" and to User and then perform a rank. This needs to cycle both user ID and transaction type or could have separate columns for each transaction type. I am wondering if I can get this (apologise for formatting). Any clues really appreciated as very new to DAX.

User ID	Transaction	Date	Rank
283	Add	05/02/2011 13:58	1
283	Spend	05/02/2011 13:59	1
293	Add	22/01/2011 21:13	1
293	Spend	04/02/2011 22:19	1
293	Refund	04/02/2011 22:20	1
293	Spend	16/06/2011 21:30	2
303	Add	24/01/2011 17:35	1
303	Spend	07/03/2011 17:36	1
303	Add	14/03/2011 09:45	2
303	Add	15/03/2011 17:20	3
303	Spend	19/03/2011 11:44	2
303	Add	19/06/2011 12:11	4
303	Add	30/12/2011 14:48	5
303	Spend	30/12/2011 14:49	3
303	Spend	01/01/2012 17:26	4

Answer 1

Assuming your table is called Table1

=CALCULATE(COUNTROWS(Table1),
           ALLEXCEPT(Table1,Table1[User ID],Table1[Transaction]),
           Table1[Date]<=EARLIER(Table1[Date])
 )

Answer 2

Sorry delay responding but got whisked off for birthday suprise by my wife. This solution failed as operation cancelled due to too many errors.

The ALLEXCEPT clause implies there is a filter for userID an transactions already in place, but I might not have been 100% clear that I'm looking for a column calculation within the powerpivot data window NOT in a pivot table. Would it be easier to just do a column ranking the "adds" for each user as I can recombine them if neccessary?  

Thans again

Mike

Answer 3

Mike,

I am implementing it as a calculated column. I'm not sure why you're getting errors. What version of PowerPivot are you running? What errors are you getting?

It works fine for me. Let me know if there are parts of the formula that you don't understand.

Answer 4

Wierd as that is exactly what I want!!!  I'm using 2012. I created the same small table you have and works perfectly. I this because I have lots of other columns in the table and that is too much for it to calculate?

Yes I would love to understand the logic behind this seems just so simple compared to what I was trying!!!!

Sorry for doubting you

Mike

Answer 5

Essentially, I'm saying: count the number of rows in the table subject to two filter expressions:

1. The [User ID] and [Transaction] both must equal the values on the current row. All other columns can have values different than the values for those columns in the current row.
2. The [Date] must be less than or equal to the [Date] on the current row.

Answer 6

Any idea why it wont calculate on my full table and only on the 3 column version? This is the error message

============================
Error Message:
============================

The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
The current operation was cancelled because another operation in the transaction failed.
The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

----------------------------
The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
The current operation was cancelled because another operation in the transaction failed.
The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.


============================
Call Stack:
============================

   at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
   at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteAMOCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
   at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
----------------------------
   at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
   at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.Recalculate()
   at Microsoft.AnalysisServices.Common.SandboxEditor.CalculateNowClicked()

============================

Answer 7

I created a new table with just these 3 columns same error. 56,000 rows with I guess date being 56,000 uniques, transaction 8 uniques and UserID maybe 5,000 uniques. File is just 2MB.  I'm using Win7 64-bit  16GB RAM but only 32-bit Excel.

Is the new RANK.EQ function any use for this as read it was introduced to avoid using the memory heavy EARLIER query, but obviously i'm talking with no relevant knowledge!

Answer 8

I don't think RANQ.EQ can give a result that is specific to the [User ID] and [Transaction].

Try this and see if it's any better than my other formula.

=RANKX(FILTER(Table1,[User ID]=EARLIER([User ID]) 
                  && [Transaction]=EARLIER([Transaction])),
      [Date],,1)

Answer 9

You probably want to try and upgrade to 64-bit Excel because the 32-bit can use less than 1GB.

Or maybe install a VM (virtual machine) with a 64-bit Excel installation that you can try out.

Answer 10

Perfect.  OK so first you create a filtered table where the UserID is the same as the first one and Transactions are the same. Date is the expression so that gets ranked against the table. Next "value" is omitted so uses the currnet row. Order is 1 so smallest date is 1.

Fantastic. Thank you so much for your help.

Mike