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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
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

DateNameIDLocationCalculation 1Calculation 2
01/11/2023AllanA1London00
02/11/2023AllanA1London11
03/11/2023AllanA1Glasgow02
04/11/2023AllanA1London23
05/11/2023JohnA2London00
06/11/2023AllanA1London34

 

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
jpessoa8
Super User
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

View solution in original post

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

 

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
Super User
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

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)

 

Dangar332_0-1699386447211.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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