Forum Discussion
Traffic Flow - Rank and Counts
SamErenberger , I think you need a rank column like
rankx(filter(Table, [User_id] =earlier([User_id])), [date time],,asc,dense)
In case you do not have a date time column
create one
date time =[date] + [time]
SamErenberger , I think you need a rank column like
rankx(filter(Table, [User_id] =earlier([User_id])), [date time],,asc,dense)
In case you do not have a date time column
create one
date time =[date] + [time]
Thank you! That works great. This also gives me a great starting point of researching more into the RANKX function!
Adding a few more comments in case anyone is looking at this topic in the future (hi future people!).
In order to only show non-consecutive location changes, since my original dataset will sometimes push a 'location change' that's in the same place as the previous location, I created two new columns:
1. repeat_row, which concatenates User ID, Location Name and the hour that they're there. It's not perfect - if someone shows up twice consecutively but over seperate hours it won't show it repeating - but it's good enough for now.
repeat_row = [user_id] & [location_name] & hour(Traffic_Flow_Data[time])2. Then I created a 'repeat rank' column which shows how many times repeat_row shows up.
repeat_value = RANKX(FILTER(Traffic_Flow_Data, Traffic_Flow_Data[repeat_row]=EARLIER(Traffic_Flow_Data[repeat_row])), [event_timestamp], , asc, Dense)Then I created a new field that concatenates user ID and date, but only if the repeat rank = 1 - meaning it's not a repeated row.
user_date = IF([repeat_rank]="1", CONCATENATE(Traffic_Flow_Data[user_id], Traffic_Flow_Data[date]), "xxx")now, in my new column loc_rank, it will rank only the users that show up. User 'xxx' will get ranked in the hundreds but I have filtered that row out in all of my reports. This is using amitchandak fabulous answer from earlier.
loc_rank = RANKX(FILTER(Traffic_Flow_Data, Traffic_Flow_Data[user_date]=EARLIER(OnsiteUnnested10k[user_date])), [event_timestamp], , asc, Dense)There's probably a less convoluted way to do this, and if you have one I'm all ears! But it's working ok for now so it's my current workaround 🙂
