Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I'm relatively new with dax formula's and trying to work my way through the data I have to become a real DAX-Master;). Bought the book and visit this website regularly, which is very helpfull! At the moment however i seem to have a lot of difficulties trying with finding returning customers. I know that there are a lot of posts out there, which propably know already, but i can't seem to find a solution to my question.
I have a calculated sales table with:
Orderdate, OrderID, ProductID, CustomerID, Orderstatus
(by the way: Multiple order lines per order ID)
I would like to know how i could get a calculated column which calculates the number of previous orders of this customers. I would really like to have it filtered to order state. Only orders with the "completed"-status should be counted.
Hope my question is clear. I tried almost everything. I can't find a good solution. I hope you guys can be of any help.
Jeroen
Solved! Go to Solution.
Hi,
Thanks for you answer. Works great to find the total number of orders, but i wanted to get the sequential number of the order.
I found this solution, which works great!
Order sequence = COUNTROWS( FILTER( CALCULATETABLE( TableOrder; ALLEXCEPT(TableOrder;TableOrder[CustomerID]) ); TableOrder[Orderdate] < EARLIER(TableOrder[Orderdate]) && TableOrder[Orderstate] = "Complete" ))+1
By the way. The +1 is used to eliminate the zero's which it returns for first-time orders;).
Thanks anyway for your help. Got me thinking:D
Jeroni
HI @JeroniJamboni,
I think it will be help if you provide some sample data to analysis.
Below is the sample measure which I haven't test on real data, maybe you can try it if it suitable for your requirement:(create table visual with 'customer id' and below measure)
Order Count = CALCULATE ( DISTINCTCOUNT ( Table[OrderID] ), FILTER ( ALL ( Table ), [CustomerID] = MAX ( Table[CustomerID] ) && [Orderstatus] = "completed" ) )
Regards,
Xiaoxin Sheng
Hi,
Thanks for you answer. Works great to find the total number of orders, but i wanted to get the sequential number of the order.
I found this solution, which works great!
Order sequence = COUNTROWS( FILTER( CALCULATETABLE( TableOrder; ALLEXCEPT(TableOrder;TableOrder[CustomerID]) ); TableOrder[Orderdate] < EARLIER(TableOrder[Orderdate]) && TableOrder[Orderstate] = "Complete" ))+1
By the way. The +1 is used to eliminate the zero's which it returns for first-time orders;).
Thanks anyway for your help. Got me thinking:D
Jeroni
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |