Filtering multiple purchases per customer on a given day

[smitre]

Hello, 

I'm trying to find a way to create a table or report that allows me to filter my data for a very specific situation. My data set includes customers who have bought and sold stock over a quarter. I need to identify customer's who have purchased or sold any given stock within the same day. 

here is a sample format of the data that I have. 

CustomerNo | Stock Symbol | Buy/Sell | Order Date | order time
1234              |AAPL               | buy        |6/30/2020   | 9:30:00

Ideally I'd like to have a table or matrix that shows me customers who have bought and sold the same stock on the same day. I can't seem to find a way to do this but I'm also very new to power BI so any help would be greatly appreciated. 

[williamadams12]

@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, 

[smitre]

I will give this a shot, thank you so much for getting me started!