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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Juan_Quikin5
Frequent Visitor

Calculated column customer bought that product for the first time

Hey guys,

I have a calendar table and a table with four columns: date, customer_id, product_id, and sales_amount.

I would like a calculated column showing 1 if that customer buys that product for the first time and 0 otherwise.

 

Any idea? Thanks guys!

1 ACCEPTED SOLUTION

It didn't work out for me, all values in the column were 1. However, you gave me a great idea! I created a table with the "groupby" part of your formula. Then, formatted the "mindate" column to have the same format as the sales table and added a column with value 1. And finally, I used the "lookupvalue" function to "merge" those two tables and it worked! Thanks @ichavarria !

View solution in original post

3 REPLIES 3
ichavarria
Solution Specialist
Solution Specialist

Hi @Juan_Quikin5,

You can use a combination of functions such as GROUPBY, MINX and IF to create a calculated column that shows 1 if a customer buys a product for the first time and 0 otherwise. Here's an example formula you might use:

 

=IF(MINX(GROUPBY('Sales', [Customer_id], [Product_id], "MinDate", MIN('Sales'[Date])),'Sales'[Date])=[Date],1,0)


This formula groups the 'Sales' table by 'Customer_id' and 'Product_id', and then finds the minimum date for each group using the MIN function. It then compares this minimum date to the date in the current row of the 'Sales' table. If they match, it returns 1, indicating that this is the first purchase for the customer and product combination, otherwise it returns 0.

Note that this formula assumes that the 'Sales' table is named "Sales" and that the columns in the formula ('Date', 'Customer_id', 'Product_id', and 'Sales_amount') have the same names as in your table. You may need to adjust the formula accordingly if your column names are different.

Also note that this formula creates a calculated column and not a measure, so it will be computed for each row in the table. If you have a large number of rows, this may impact performance.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly

It didn't work out for me, all values in the column were 1. However, you gave me a great idea! I created a table with the "groupby" part of your formula. Then, formatted the "mindate" column to have the same format as the sales table and added a column with value 1. And finally, I used the "lookupvalue" function to "merge" those two tables and it worked! Thanks @ichavarria !

Glad to hear that it, at least, routed to finding the right solution for you! 



Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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