Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have data that is broken out by transactions by type via date as well as a user ID. I was hoping to find a way to get the count of transactions done in the same day per user ID. Further, I would like the number of User IDs that had more than 1 transaction in the same day.
Solved! Go to Solution.
Thank you, I think I've got it now.
Check the attached PBIX file.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
User ID | Date | Tranaction |
AAA | 1/1/2022 | A |
AAA | 1/1/2022 | B |
AAA | 2/2/2022 | C |
BBB | 1/1/2022 | D |
BBB | 1/5/2022 | D |
CCC | 1/1/2022 | A |
CCC | 1/1/2022 | B |
CCC | 1/2/2022 | C |
CCC | 1/2/2022 | C |
I am not sure how to paste sample data but this is generally what the data is like. I want to count how mant transactions are in the same day by each user. Also I want to count how many users had 2+ transactions in the same day.
Have a look at the attached PBIX file and see if it helps.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
The calculation does not seem to work. It shows 3 users with more than 1 transaction in a day but only AAA and CCC did more than transaciton in a day. I am looking for specifically the count of users with subsequent transactions in the same day.
Apologies, try the attached (updated) PBIX.
Just check to make sure it works in all of your scenarios.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
This works great, one last request: how would I get a count of the subsequetn transactions that were in the same day. So for this test data, A would have 1, C would have 2, so a count of 3 and say if A had 3 tranasctions done on the same day the count would hit 2 for a total of 4. Is this the previous calulation you did?
Thanks!
I'm not sure if I fully understand but I've added the original calc back in and called it '
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Sorry for the late response but the original calc does not seem to do it. I am looking for the count of subsequent transactions done in a day. So if User AAA would have had 3 transactions in one day, then 2 in another day, they would have done 3 total subsequent transactions.
Sorry, I don't understand.
"So if User AAA would have had 3 transactions in one day, then 2 in another day, they would have done 3 total subsequent transactions."
How does the 3 in one day and the 2 in another equal the 3 in total?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
In this example user AAA had 2 subsequent transactions in one day, then they had one subsequent transaction on another day. This gets us our 3 total subsequent transactions.
In my use case, subsequent transaction = sum of all transactions done on the same day after the first transaction
To give some context, a process was changed to allow more than one transaction to happen in a day per user. I want to find out the sum of these subsequent transactions that are now being done that could not be done before. The first transaction could always be done, the subsequent transactions in the same day could not.
Sorry, I'm still not following.
In the summary data provided, user AAA only had one transaction on the second day, how is that considered 'subsequent' as it is the first for that day?
User ID | Date | Tranaction |
AAA | 1/1/2022 | A |
AAA | 1/1/2022 | B |
AAA | 2/2/2022 | C |
BBB | 1/1/2022 | D |
BBB | 1/5/2022 | D |
CCC | 1/1/2022 | A |
CCC | 1/1/2022 | B |
CCC | 1/2/2022 | C |
CCC | 1/2/2022 | C |
And for this example data, what would the subsequent transactions for CCC be?
2 right?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
In the example data AAA only has one subsequent transaction and CCC has 2, that is correct, for a total of 3. Sorry if it was not clear, I just wanted to explain another situation with more subsequent transactions so that you were clear what it meant. With the current data set the first calculation you did was correct in the result of 3 but the calculation was off when I tried to add more subsequent transactions. That's why I did another hyptohetical sitaution
Ok, makes sense.
Can you please paste some sample data that covers these other scenarios and I'll add it in to the solution.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
User | Date | Transaciton |
AAA | 1/1/2022 | A |
AAA | 1/1/2022 | B |
AAA | 1/1/2022 | C |
BBB | 1/1/2022 | A |
BBB | 1/5/2022 | A |
CCC | 1/1/2022 | A |
CCC | 1/1/2022 | B |
CCC | 1/5/2022 | A |
CCC | 1/5/2022 | C |
DDD | 1/2/2022 | A |
DDD | 1/2/2022 | B |
DDD | 1/2/2022 | C |
DDD | 1/2/2022 | D |
Here is some data that should work, AAA should show 2 subsequent transactions, CCC should show 2 subsequent transactions and DDD should show 3 subsequent transactions for a total of 7.
Thank you, I think I've got it now.
Check the attached PBIX file.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Thank you for all your help!
Hey @trevordunham,
I see that this was marked as solved by a CST member, not you.
I'm just checking, is the issue actually solved for you?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Yes, they just beat me to it. Appreciate it
Awesome, just making sure. Sometimes they get a little eager before the issue is solved.
Glad I could help.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Thanks for the data, that helps.
What do you want tthe output to look like? i.e. is it going in a card, table, something else?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Paste some sample data (not a screenshot) that includes all these scenarios and I'm sure you'll get an answer quickly.
A screenshot of your data model would also be useful.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
70 | |
67 |