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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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