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.
Hi,
I need help with calculation every time tool status changes. I have many tool IDs set in the same table similar to the one below. What would be the best way to count where every time Availability column changes.
Sample Data Set:
Tool | DateTime | Availability | Count |
AA1 | 8/20/22 11:49 PM | D | 1 |
AA1 | 8/20/22 10:08 PM | U | |
AA1 | 8/20/22 2:08 PM | U | |
AA1 | 8/19/22 7:11 PM | D | 1 |
AA1 | 8/18/22 9:06 PM | D | |
AA1 | 8/18/22 8:15 PM | U | |
AA1 | 8/18/22 6:15 PM | D | 1 |
BB1 | 8/20/22 11:08 PM | D | 1 |
BB1 | 8/20/22 10:08 PM | D | |
BB1 | 8/20/22 6:45 PM | U | |
BB1 | 8/20/22 5:12 PM | D | 1 |
BB1 | 8/20/22 2:08 PM | U | |
BB1 | 8/19/22 7:11 PM | D | 1 |
BB1 | 8/18/22 9:06 PM | U | |
BB1 | 8/18/22 8:15 PM | U | |
BB1 | 8/18/22 6:15 PM | D | 1 |
Solved! Go to Solution.
It works but it's taking a long time to load 800k+ rows for the index column. Is there an alternative way to work on this?
write what result you expect
Tool | DateTime | Availability | Count |
AA1 | 8/20/22 11:49 PM | D | 1 |
AA1 | 8/20/22 10:08 PM | U | |
AA1 | 8/20/22 2:08 PM | U | |
AA1 | 8/19/22 7:11 PM | D | 1 |
AA1 | 8/18/22 9:06 PM | D | |
AA1 | 8/18/22 8:15 PM | U | |
AA1 | 8/18/22 6:15 PM | D | 1 |
BB1 | 8/20/22 11:08 PM | D | 1 |
BB1 | 8/20/22 10:08 PM | D | |
BB1 | 8/20/22 6:45 PM | U | |
BB1 | 8/20/22 5:12 PM | D | 1 |
BB1 | 8/20/22 2:08 PM | U | |
BB1 | 8/19/22 7:11 PM | D | 1 |
BB1 | 8/18/22 9:06 PM | U | |
BB1 | 8/18/22 8:15 PM | U | |
BB1 | 8/18/22 6:15 PM | D | 1 |
Based on the sample above, I'd like to see AA1 = 3 and BB1 = 4.
The logic of the calculation is not yet clear to me.
tell me why these changes were not counted
This is related to tool status up and down.
D = Down
U = Up
From the sample data I provided.
Tool | DateTime | Availability | Count |
AA1 | 8/20/22 11:49 PM | D | 1 |
AA1 | 8/20/22 10:08 PM | U | |
AA1 | 8/20/22 2:08 PM | U | |
AA1 | 8/19/22 7:11 PM | D | 1 |
AA1 | 8/18/22 9:06 PM | D | |
AA1 | 8/18/22 8:15 PM | U | |
AA1 | 8/18/22 6:15 PM | D | 1 |
BB1 | 8/20/22 11:08 PM | D | 1 |
BB1 | 8/20/22 10:08 PM | D | |
BB1 | 8/20/22 6:45 PM | U | |
BB1 | 8/20/22 5:12 PM | D | 1 |
BB1 | 8/20/22 2:08 PM | U | |
BB1 | 8/19/22 7:11 PM | D | 1 |
BB1 | 8/18/22 9:06 PM | U | |
BB1 | 8/18/22 8:15 PM | U | |
BB1 | 8/18/22 6:15 PM | D | 1 |
Any "D" that are together only count as one same for "U".
If I'm looking at AA1, I have 3 times tool down and 2 times tool up.
As for BB1, I have 4 times tool down and 3 times tool up.
Data is usually added when people give updates so that's why we have multiple rows with D and U together.
It doesn't matter which one I'm counting within the group. I just need to count one if they are together.
User | Count |
---|---|
89 | |
72 | |
69 | |
65 | |
57 |
User | Count |
---|---|
96 | |
92 | |
91 | |
75 | |
69 |