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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
IliasKatsis
Frequent Visitor

Check if a product was bought in promo or not

Hello everyone,

 

I have a dataset with receipts. Each line have a receipt date, a user id which represents ther person who enter the receipt in the system, a receipt id and the general information about every product ( Category id , product id , brand id etc), another fact is that wer know the retailer for every receipt. Every line represents a single product, a sample of the data is like this data.jpg

 

Now i have another table, which represents promo data and is like this:

IliasKatsis_1-1679958278161.png

 

The objective is to combine this two tables. I want to check that if a product in a specific receipt date was bought in promo or not.

The statements is that the
1.receipt date must be inside the start and end date of the promo

2. the retailer id of the receipt is thr same with the promo

3. of course the product id must be the same in both tables.

 

Please help me

Thanks

1 ACCEPTED SOLUTION
littlemojopuppy
Community Champion
Community Champion

That would be a really large table.  Instead of combining tables, try creating a measure to determine if a receipt qualifies.

Two steps:

  1. FILTER() promo for where Retailer ID matches on both tables, Product ID matches, and receipt date >= promo start date and <= promo end date. 
  2. Use COUNTROWS() on that.

If COUNTROWS > 1 then you know it was bought in a promo.

 

Hope this helps!

View solution in original post

3 REPLIES 3
littlemojopuppy
Community Champion
Community Champion

@IliasKatsis glad I could help!

IliasKatsis
Frequent Visitor

Thanks! Very good!

littlemojopuppy
Community Champion
Community Champion

That would be a really large table.  Instead of combining tables, try creating a measure to determine if a receipt qualifies.

Two steps:

  1. FILTER() promo for where Retailer ID matches on both tables, Product ID matches, and receipt date >= promo start date and <= promo end date. 
  2. Use COUNTROWS() on that.

If COUNTROWS > 1 then you know it was bought in a promo.

 

Hope this helps!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.