Running Count by Category in POWER PIVOT Data Model

%3CLINGO-SUB%20id%3D%22lingo-sub-1362491%22%20slang%3D%22en-US%22%3ERunning%20Count%20by%20Category%20in%20POWER%20PIVOT%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1362491%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20a%20Table%20with%20timestamps%20(TS)%20and%20categories%20(UserID)%20and%20want%20to%20%3CEM%3Ecreate%20an%20additional%20column%20(EventID)%3C%2FEM%3E%20with%20an%20ID%20that%20is%20runs%20from%201%20to%20n(UserID)%20individually%20for%20each%20category%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETS%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BUserID%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CEM%3EEventID%3C%2FEM%3E%20%26lt%3B-%20to%20be%20calculated%3C%2FP%3E%3CP%3E01%3A12%26nbsp%3B%2000001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CEM%3E001%3C%2FEM%3E%3C%2FP%3E%3CP%3E01%3A14%26nbsp%3B%2000001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CEM%3E002%3C%2FEM%3E%3C%2FP%3E%3CP%3E01%3A15%26nbsp%3B%2000001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CEM%3E003%3C%2FEM%3E%3C%2FP%3E%3CP%3E01%3A11%26nbsp%3B%2000002%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CEM%3E001%3C%2FEM%3E%3C%2FP%3E%3CP%3E01%3A14%26nbsp%3B%2000002%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CEM%3E002%3C%2FEM%3E%3C%2FP%3E%3CP%3E01%3A16%26nbsp%3B%2000002%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CEM%3E003%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20current%20attempt%20looks%20like%20this%2C%20but%20the%20calculation%20doesn't%20run%20through%20and%20the%20program%20stops%20responding%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3DCALCULATE%0A(%0Acount(%5BUserID%5D)%2C%0Afilter(%0A%20%20%20%20%20%20%20%20%20ALLEXCEPT('DB'%2C%20'DB'%5BUserID%5D)%2C%0A%20%20%20%20%20%20%20%20%20%5BTS%5D%20%26lt%3B%3D%20earlier(%5BTS%5D)%0A%20%20%20%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20something%20of%20about%20330k%20rows%2C%20but%20I'd%20like%20it%20to%20be%20able%20to%20handle%20more%20than%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20case%20it's%20not%20obvious%2C%20I'm%20rather%20new%20at%20this%20and%20google%20most%20of%20my%20code.%20Thanks%20a%20lot%20in%20advance%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1362491%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hello,

 

I am having a Table with timestamps (TS) and categories (UserID) and want to create an additional column (EventID) with an ID that is runs from 1 to n(UserID) individually for each category:

 

TS       UserID     EventID <- to be calculated

01:12  00001      001

01:14  00001      002

01:15  00001      003

01:11  00002      001

01:14  00002      002

01:16  00002      003

 

My current attempt looks like this, but the calculation doesn't run through and the program stops responding:

 

 

=CALCULATE
(
count([UserID]),
filter(
         ALLEXCEPT('DB', 'DB'[UserID]),
         [TS] <= earlier([TS])
       )
)

 

 

I have something of about 330k rows, but I'd like it to be able to handle more than that.

 

In case it's not obvious, I'm rather new at this and google most of my code. Thanks a lot in advance for your help!

0 Replies