Forum Discussion
Filtering multiple purchases per customer on a given day
smitre - I'd start with creating a data model, so focus on the relationships you want to create which would give you the ability to slice and filter on the values you need. In your case, you need common values for the order type, date and customer number. So, think of the orders themselves as one (or maybe two) distinct fact tables and the order dates, order types and customer numbers as dimensions you can connect to the fact table(s).
If you retain all of the data in a single fact table, you'd have to write some DAX measures for buy orders and sell orders separately in order to properly slice/filter the data accordingly, but for the purpose of a simpler answer here just split the order types into two distinct fact tables for buy and sell.
Buy Table:
Sell Table:
Connect the Facts to the Dimensions: Date, Customer No, and Order Type:
Create Matrix Table:
You can toggle/filter the order type of buy/sell to count the orders for each customer for each date in the matrix to determine which customers have multiple transactions per day. This is what you wanted, but keep in mind it may worth it to consider the granularity of your date slice and whether you want to incorporate a more robust calendar table, which you can of course drop in as rows or columns. Just depends on what you're trying to analyze and why.
Hope this helps,
- smitre4 years agoNew Member
I will give this a shot, thank you so much for getting me started!