Counting previous occurrences in Dax

[5foot9]

[FONT=&quot]I have a column called 'slots' containing values from 1 to 100 which populate the column any amount of times and in any order. They are sorted by date but I don't know if it's possible to use EARLIER to count previous occurrences of each slot by row as there are often multiple occurrences for each date, ideally I would like to avoid relying on dates. [/FONT]

[FONT=&quot]So I am trying to count each occurrence of a slot number row by row in a calculated column to eventually calculate sequences and frequencies.[/FONT]

[FONT=&quot]This is what I've been trying but gives me the total count of each slot number each time. If anyone is able to point me in the right direction it would be greatly appreciated.[/FONT]

[FONT=&quot][=CALCULATE(COUNT([Slot]),FILTER(Records,Records[Slot]<=EARLIER(Records[Slot])))]

[/FONT]I've posted the same post here too - PowerPivot Forum - Login

By the way I'm using powerpivot 2016

 

[gazpage]

You literally want to look at the rows above the current row in the table? As far as I am aware PowerPivot doesn't even consider the position of a row.

In order to do what you want to do you would need to add an index column to you data. You could easily do that with Power Query if needed. Then use your filter logic above with earlier to pick up the row above.

NB that you probably only want an = in the part above to look for rows with the same value in the slot column.

 

[5foot9]

Thanks for your reply, I'm new to PP and have seen a few mentions of index columns and power query, which led me here - https://powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/

I'm going to go for building the table in excel then adding it to the data model, this is probably the best thing to do at this stage.

 

[gazpage]

I had a quick look and he mentions that you can use EARLIER to look at the previous row, but does not show how to actually do it. His first link is just a description of EARLIER and the second is a link to his book...

You could just add an index column in excel and then bring that into PP. The Power Query way is pretty simple and I could explain that if it helped.

Once you have an index column you want something like.

CALCULATE ( 
    COUNTROWS ( Records ),
    FILTER ( ALL ( Records ),
        [COLOR=#333333]Records[Slot]=EARLIER ( Records[Slot] ) &&
        Records[Index] < EARLIER ( Records[Index]
    )
)
[/COLOR]

 

[5foot9]

Ok....I went round in circles with this late last night and gave up!....
I think sometimes when I'm faced with so much information I can't see the wood for the trees!

I added an index in excel and entered your DAX, I had to make'<=' as the original was not in the correct row. The problem I found then was it was picking total count of slot, not just losers which is what I actually want! I've added that in as you can see below and ideally I would like the blanks populated with 0 but what its doing now is just counting all the losers and not giving me streaks or sequences.

=CALCULATE (     COUNTROWS ( Records ),
    FILTER ( ALL ( Records ),
        Records[Slot]=EARLIER ( Records[Slot] ) &&
        EARLIER(Records[Lost])=1 && Records[Index] <= EARLIER ( Records[Index]
    )
))

I have made this work in an excel table where the result triggers a reset in the sequence - I don't know if this is possible with PP.
I don't have permission to post attachments unfortunately hopefully this will do -

EXCEL		POWER PIVOT
Date	ID	slot	count of slot	Lost	LossSeq		Date	index	slot	Lost	SeqTest
12/04/2017	11-370	11	370	1	3		12/04/2017	8557	11	1	370
18/04/2017	11-371	11	371	0	0		18/04/2017	8615	11	0
19/04/2017	11-372	11	372	1	1		19/04/2017	8625	11	1	372
19/04/2017	11-373	11	373	1	2		19/04/2017	8626	11	1	373
19/04/2017	11-374	11	374	1	3		19/04/2017	8627	11	1	374
19/04/2017	11-375	11	375	1	4		19/04/2017	8628	11	1	375
20/04/2017	11-376	11	376	1	5		20/04/2017	8633	11	1	376
20/04/2017	11-377	11	377	1	6		20/04/2017	8634	11	1	377
21/04/2017	11-378	11	378	1	7		21/04/2017	8639	11	1	378
22/04/2017	11-379	11	379	1	8		22/04/2017	8652	11	1	379
22/04/2017	11-380	11	380	1	9		22/04/2017	8653	11	1	380
25/04/2017	11-381	11	381	0	0		25/04/2017	8674	11	0
29/04/2017	11-382	11	382	1	1		29/04/2017	8706	11	1	382
30/04/2017	11-383	11	383	1	2		30/04/2017	8707	11	1	383
30/04/2017	11-384	11	384	1	3		30/04/2017	8708	11	1	384
01/05/2017	11-385	11	385	0	0		01/05/2017	8716	11	0
01/05/2017	11-386	11	386	1	1		01/05/2017	8717	11	1	386
03/05/2017	11-387	11	387	1	2		03/05/2017	8727	11	1	387
03/05/2017	11-388	11	388	1	3		03/05/2017	8728	11	1	388
05/05/2017	11-389	11	389	1	4		05/05/2017	8739	11	1	389

<colgroup><col width="75" style="width:56pt"> <col width="64" style="width:48pt" span="2"> <col width="99" style="width:74pt"> <col width="64" style="width:48pt"> <col width="71" style="width:53pt" span="2"> <col width="75" style="width:56pt"> <col width="64" style="width:48pt" span="4"> </colgroup><tbody>
</tbody>

 

[gazpage]

You can make a measure that returns a blank return a 0 instead by simply adding + 0 to it.