count of previous entries

[Anonymous]

I have tried to get help with this solution before and may not have worded it correctly, so trying again.

I have a table where I need to add two calculated columns. Ive called them Calculation 1 and calculation 2

here is the table

Date	Name	ID	Location	Calculation 1	Calculation 2
01/11/2023	Allan	A1	London	0	0
02/11/2023	Allan	A1	London	1	1
03/11/2023	Allan	A1	Glasgow	0	2
04/11/2023	Allan	A1	London	2	3
05/11/2023	John	A2	London	0	0
06/11/2023	Allan	A1	London	3	4

Using the ID field as a key field, I am trying to achieve the following.

Calculation 1 to be a count of previous times that ID has been at that location.

Calculation 2 to be a count of previous times that ID has been at all locations.

For example, on 4/11/23, ID1 (Allan) has been to London twice previously, so the entry is 2 in the calculation 1 column, however he has also been to Glasgow on one occaision, therefore, calculation 2 has a count of 3. Twice for London plus one for Glasgow.

The table will have approx 300000 entries, where there will individuals will not make visits for weeks. 

I have tried to count rows and been given suggestions here previously which, while grateful, have been unable to get working.

These do need to be calculated columns as i need to be able to look at a particular status at the time of entry as it will also be linked to another table which will use the ID field and different dates.

I appreciate any guidance or help.

Lola

[Anonymous]

Hi @Anonymous ,

Please have a try.

Table =
SUMMARIZE (
    'YourTable',
    'YourTable'[ID],
    'YourTable'[Location],
    "Count of Visits",
        COUNTROWS (
            FILTER (
                ALL ( 'YourTable' ),
                'YourTable'[ID] = EARLIER ( 'YourTable'[ID] )
                    && 'YourTable'[Location] = EARLIER ( 'YourTable'[Location] )
                    && 'YourTable'[Date] < EARLIER ( 'YourTable'[Date] )
            )
        ),
    "Count of Unique Locations",
        COUNTROWS (
            DISTINCT (
                FILTER (
                    ALL ( 'YourTable' ),
                    'YourTable'[ID] = EARLIER ( 'YourTable'[ID] )
                        && 'YourTable'[Date] < EARLIER ( 'YourTable'[Date] )
                )
            )
        )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jpessoa8 · Accepted solution]

Hello @Anonymous ,

I definitely would not recommend the creation of calculated columns for these calculations, especially when you mention that the table has already more than 300k records.

Nonetheless, when creating calculated columns that need to be grab the “context” from the same row, the approach should be creating variables to “store” the value of the needed columns and afterwards apply it in a filter inside a calculate.

In your case, this should return the results you want:

Calculation1 = 
var _ID = 'Table'[ID]
var _Date = 'Table'[Date]
var _Location = 'Table'[Location]
return
CALCULATE( COUNTROWS( 'Table' ) , FILTER( 'Table' , 'Table'[ID] = _ID && 'Table'[Location] = _Location && 'Table'[Date] < _Date )) + 0

Calcultaion2 = 
var _ID = 'Table'[ID]
var _Date = 'Table'[Date]
return
CALCULATE( COUNTROWS( 'Table' ) , FILTER( 'Table' , 'Table'[ID] = _ID && 'Table'[Date] < _Date )) + 0

I've added a "+0" at the end to avoid the nulls.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa

[Dangar332]

Hi, @Anonymous 

try below code 

Column1 = 
var a = 'Table'[ID]
var b = 'Table'[Location]
var c = 'Table'[Date]
var d =COUNTX(FILTER('Table','Table'[ID]=a && 'Table'[Location]=b && 'Table'[Date]<c),'Table'[ID])
return
IF(d=BLANK(),0,d)

AND

Column2 = 
var a = 'Table'[ID]
var b = 'Table'[Date]
var c= COUNTX(FILTER('Table','Table'[ID]=a && 'Table'[Date]<b),'Table'[ID])
return
IF(c=BLANK(),0,c)