Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## count of previous entries

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

1 ACCEPTED SOLUTION
Super User

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

3 REPLIES 3
Anonymous
Not applicable

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] )
)
)
)
)
``````

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.

Super User

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

Super User

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)``````

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors