DAX: Ranking of individuals

[Fridm]

Hi everyone,

I have some troubles with PowerBI that I hope someone here can help me with. I have a list of People entering and exiting locations with timestamps that I am trying to convert to a format that would work with Sankey/Chord charts:

Current dataset

UserProfileId	AreaName	EnterUtcDate"	EnterUtcTime	ExitUtcDate	ExitUtcTime
963566	X1	08.06.2016	10:43:01	08.06.2016	10:43:32
963566	X2	08.06.2016	10:44:20	08.06.2016	10:44:54
961627	X1	08.06.2016	10:50:18	08.06.2016	10:52:31
961627	X2	08.06.2016	11:09:16	08.06.2016	11:09:16
961627	X3	08.06.2016	11:10:58	08.06.2016	11:12:28
961627	X4	08.06.2016	11:45:22	08.06.2016	11:46:43
961627	X5	08.06.2016	12:03:08	08.06.2016	12:04:01

The format I need to have the data in to do sankay/chord charts is

To	From	Amount

The solution I have come up with is to rank the each row based on individual and time of entry, an afterwards make a lookup on the individuals "next location".

UserProfileId	AreaName	EnterUtcDate"	EnterUtcTime	ExitUtcDate	ExitUtcTime	Rank	Next Area
963566	X1	08.06.2016	10:43:01	08.06.2016	10:43:32	1	X2
963566	X2	08.06.2016	10:44:20	08.06.2016	10:44:54	2	-
961627	X1	08.06.2016	10:50:18	08.06.2016	10:52:31	1	X2
961627	X2	08.06.2016	11:09:16	08.06.2016	11:09:16	2	X3
961627	X3	08.06.2016	11:10:58	08.06.2016	11:12:28	3	X4
961627	X4	08.06.2016	11:45:22	08.06.2016	11:46:43	4	X5
961627	X5	08.06.2016	12:03:08	08.06.2016	12:04:01	5	-

Afterwards I imagine I could use some type of measure with a count formula to summarize the people entering and exiting a location.

My issue is that I cant seem to get the rank formula to work since I am not very profficient with PowerBI. I have been trying to use variations of RANKX: but seem to either rank the whole table, or each row individually. Can someone help me with how I can make a formula that only rank the results of each individual? Or can anyone come up with a better solution to my problem?

I might also ad that I have been making new columns converting the timestamps to digital formats, so I shouldnt have any issues with the date/time formats..

Thank you in advance!

[v-qiuyu-msft · Accepted solution]

Hi @Fridm,

In your scenario, you can specify the RANKX( ) function to rank records with each [UserProfileId] instance. See:

Rank = RANKX(
         FILTER(
         ALLSELECTED(Table1),Table1[UserProfileId]=EARLIER(Table1[UserProfileId])
                   ),
         'Table1'[EnterUtcTime],
     ,1)

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Fridm]

Thank you, this works great!

Best regards,

Michael