Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, I have a data frame which has 3 variables i.e. Id, Event and time for each id there can be multiple event happening and for each event date time factor has been added to find out at what time which event happen. My data frame looks like this as below.
Id | Event | Time(DD/MM/YYYY HH:MM:SS) | Output | |
1 | event1 | 4/20/2022 1:09:47 AM | 1 | |
1 | event2 | 4/20/2022 1:12:23 AM | 2 | |
1 | event3 | 4/20/2022 1:13:35 AM | 3 | |
1 | event4 | 4/20/2022 1:13:36 AM | 4 | |
1 | event5 | 4/20/2022 1:13:37 AM | 5 | |
1 | event4 | 4/20/2022 1:15:17 AM | 4 | |
1 | event3 | 4/20/2022 5:56:35 AM | 3 | |
1 | event5 | 4/20/2022 5:56:35 AM | 5 | |
2 | event1 | 4/26/2022 11:19:00 AM | 1 | |
2 | event2 | 4/26/2022 11:20:05 AM | 2 | |
2 | event3 | 4/26/2022 11:20:46 AM | 3 | |
2 | event5 | 4/26/2022 11:20:50 AM | 4 |
Added blank column so that there could be space between event and Time column(Ignore)
The difference in event can be seconds, minute or hours or days.
My Aim is to based on my Id column I need to add rank order to the events based on Date time factor. So for each Id rank order should starts with 1.
And if the event is repetated like for example consider event4 for Id 1 it has been repetated twice so it should pick first category value assigned to it.
Any help would be appreciated
Thanks in advance
Solved! Go to Solution.
Hi,
Thank you for your message.
Please check the below picture and the attached pbix file.
Expected result CC =
VAR _summarizetable =
GROUPBY (
Data,
Data[Id],
Data[Event],
"@time_earliest", MINX ( CURRENTGROUP (), Data[Time(DD/MM/YYYY HH:MM:SS)] )
)
RETURN
RANK (
SKIP,
_summarizetable,
ORDERBY ( [@time_earliest], ASC ),
PARTITIONBY ( Data[Id] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
HI,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Expected result CC =
RANK (
SKIP,
SUMMARIZE ( Data, Data[Id], Data[Event], Data[Time(DD/MM/YYYY HH:MM:SS)] ),
ORDERBY ( Data[Time(DD/MM/YYYY HH:MM:SS)], ASC, Data[Event], ASC ),
PARTITIONBY ( Data[Id] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, Sorry missed 1 part my Expected Output should be same as output column in the table which I have added above.
Logic behind the output is "if the event is repetated like for example consider event4 for Id 1 it has been repetated twice so it should pick first category value assigned to it."
Hi,
Thank you for your message.
Please check the below picture and the attached pbix file.
Expected result CC =
VAR _summarizetable =
GROUPBY (
Data,
Data[Id],
Data[Event],
"@time_earliest", MINX ( CURRENTGROUP (), Data[Time(DD/MM/YYYY HH:MM:SS)] )
)
RETURN
RANK (
SKIP,
_summarizetable,
ORDERBY ( [@time_earliest], ASC ),
PARTITIONBY ( Data[Id] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |