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.
Hello Community,
Thanks so much for taking the time to review this, I have 3 tables as follows:
Sales (SaleID, DateOfSale, Customer, Quantity)
Activity (SaleID, DateOfActivity, Customer)
Customer (CustomerID, FullName)
I would like to track the activities that precedes a Sale, this means that someone from the company contact our customer prior to a Sale occurs, in the DB there are records when even though a customer has an activity related they never do a purchase, I want to get just those activities that has a Activity BEFORE the Sale occurs, If theres more than one Activity previous to the sale should be also consider.
For example in the previous image, a sale occurs on 2/4/2023 and also an Activity prior to that Sale on 2/1/2023. I want to consider only those cases, Any activity that occurs prior to a Sale and highlighted in a Matrix just as in the image.
Thanks for any recommendation or help.
Solved! Go to Solution.
@GuidoPinares , You can have new column like
New column =
var _cnt = countx(filter(Activity, Activity[Customer] = sales[customer] && Activity[Date] < sales[Date]) , Activity [Customer])
var _cntSalesbefore = countx(filter(sales, sales[Customer] = earlier(sales[customer]) && sales[Date] < earlier(sales[Date])) , sales[Customer])
return
if(isblank(_cntSalesbefore ), _cnt, blank())
Returning count only when there are no sales before
refer if needed
refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s
Thanks so much, the videos you sent help to understand even more the solution.
@GuidoPinares , You can have new column like
New column =
var _cnt = countx(filter(Activity, Activity[Customer] = sales[customer] && Activity[Date] < sales[Date]) , Activity [Customer])
var _cntSalesbefore = countx(filter(sales, sales[Customer] = earlier(sales[customer]) && sales[Date] < earlier(sales[Date])) , sales[Customer])
return
if(isblank(_cntSalesbefore ), _cnt, blank())
Returning count only when there are no sales before
refer if needed
refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |