Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to 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 !
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:
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
84 | |
64 | |
52 | |
49 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |