Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Thank you, this works great!
Best regards,
Michael
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |