Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trevordunham
Helper III
Helper III

Help with counts of transactions done in a day broken out by a field

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.

1 ACCEPTED 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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

20 REPLIES 20
trevordunham
Helper III
Helper III

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 '

Count of Multiple Transactions per Day'. See attached.
It may not be quite what you're after but let me know.
 
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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

UserDateTransaciton
AAA1/1/2022A
AAA1/1/2022B
AAA1/1/2022C
BBB1/1/2022A
BBB1/5/2022A
CCC1/1/2022A
CCC1/1/2022B
CCC1/5/2022A
CCC1/5/2022C
DDD1/2/2022A
DDD1/2/2022B
DDD1/2/2022C
DDD1/2/2022D

 

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
KNP
Super User
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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.