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.
Dear kind sir/madam,
I hava a nice challenge for you:
KEY | SEQUENCE | TIME | RESULT |
|
|
SUP-1111 | 22 | 6 |
|
|
|
SUP-1111 | 23 | 0,5 |
|
|
|
SUP-1111 | 24 | 3 | 3 |
|
|
SUP-2222 | 30 | 5 | 5 |
|
|
SUP-2222 | 31 |
|
|
|
|
SUP-2222 | 32 |
|
|
|
|
SUP-3333 | 22 | 2 |
|
|
|
SUP-3333 | 23 | 9 |
|
|
|
SUP-3333 | 24 |
|
|
|
|
SUP-3333 | 25 |
|
|
|
|
SUP-3333 | 26 | 8 |
|
|
|
SUP-3333 | 27 | 4,5 | 4,5 |
|
|
As you can see I got a table with 3 columns. The first column specify the keys. The second column specify the sequence for each row related to the key. The sequence is not unique for the whole table, but is unique per key. The third column shows the time that is related to each sequence, sometimes it is filled and sometimes not.
What I need is to get column with the result. This result is only one value per distinct key. It is not always related to the highest sequence, because sometimes the highest sequence is empty. It also is NOT the highest time. It is the most recent time from the last sequence that has a time filled in. I hope the table above makes it clear.
I hope you can help me, Thanks in advance 🙂
Solved! Go to Solution.
@SearchKnowledge , Create a new column like
result =
var _max = maxx(filter(Table, not(isblank([Time])) && [key] =earlier([Key])),[Sequence])
return
maxx(filter(Table, [Sequence] =_max && [key] =earlier([Key])),[Time])
@SearchKnowledge , Create a new column like
result =
var _max = maxx(filter(Table, not(isblank([Time])) && [key] =earlier([Key])),[Sequence])
return
maxx(filter(Table, [Sequence] =_max && [key] =earlier([Key])),[Time])
@amitchandak I am trying to understand how this works. But what is var_max exactly returning? A true or false statement or a table??
User | Count |
---|---|
52 | |
46 | |
20 | |
16 | |
13 |
User | Count |
---|---|
108 | |
54 | |
31 | |
20 | |
18 |