Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |