cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

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
Community Support

Hi @Anonymous ,

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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors