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

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

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
v-rongtiep-msft
Community Support
Community Support

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors