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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!