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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
smitre
New Member

Filtering multiple purchases per customer on a given day

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. 

 

2 REPLIES 2
williamadams12
Resolver I
Resolver I

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

williamadams12_0-1656610383371.png

 

Sell Table: 

williamadams12_1-1656610431956.png

 

Connect the Facts to the Dimensions: Date, Customer No, and Order Type:

williamadams12_2-1656610503810.png

 

 

Create Matrix Table: 

williamadams12_3-1656610589880.png

 

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, 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors