cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Creating a new column based on 2 column value (timestamp and ID)

Hi guys,

I have data table like this

Client IDTimestampActionStep
11action11
112action22
113action33
27action21
28action32
310action21
312action32
313action53
315action104

 

And I want to create a column "Step" where will be values based on unique ID and timestamp, smt like "look at the column Client ID, then look for timestamp and to the first action give 1, to the second action 2, etc" so when I want to see for example which actions users made in step 3 in the whole process, I can just filter it and to see that action3 and action5 was made once by 2 users.

 

Thanks

Lukas

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous

 

Step =
CALCULATE (
    COUNT ( Table1[Timestamp] ),
    ALLEXCEPT ( Table1, Table1[Client ID] ),
    Table1[Timestamp] <= EARLIER ( Table1[Timestamp] )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

Hi @Anonymous

 

Step =
CALCULATE (
    COUNT ( Table1[Timestamp] ),
    ALLEXCEPT ( Table1, Table1[Client ID] ),
    Table1[Timestamp] <= EARLIER ( Table1[Timestamp] )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

View solution in original post

Anonymous
Not applicable

Hi AIB,

 

thanks for formula but I encountered another problem. The PBIX file has only 1,3 MB (the table has only 17029 rows and 9 columns) but when I write the formula I got the error message "There's not enough memory to complete this operation. Please try again later when there may be more memory available.". I checked other threads regarding this error but I dont understand why I got it - I have 8GB RAM (in task manager I can see that BI during calculation is using 3-4 GB), I have 64-bit version of Power BI. 

 

When I filtered some of the rows and got about 15k rows everything was just perfect. Any idea how to deal with this?

 

Thank you

Lukas

@Anonymous 

Not really. It doesn't look like something that could cause the system to run out of memory

If you can share the pbix I'll try it on my machine

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

Anonymous
Not applicable

Hi AIB,

 

unfortunately that will not be possible for now. I have to recreate it with random numbers. But for now at least I know the answer how to do it.

 

Thanks again

Best

Lukas

IanCockcroft
Helper V
Helper V

Hi Lukas, if I undertand the issue correctly, create  custom column in query editor and write code to build he new column.

cheerd

ian

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.