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.
I have two tables:
1) Product Purchase Table
Customer | Product | Purchase Date |
18370 | A | 2016-05-02 |
18370 | A | 2016-04-12 |
18370 | C | 2016-03-10 |
16129 | A | 2016-08-11 |
15691 | B | 2016-09-10 |
2) Customer Contact Table (the date we met with the customer to recommend products)
Customer | Product | Contact Date | Result I Want To Find: A purchase of this product was made within one month after contact |
18370 | C | 2016-04-01 | FALSE |
18370 | B | 2016-04-01 | FALSE |
18370 | A | 2016-04-01 | TRUE |
16129 | A | 2016-07-06 | FALSE |
15691 | B | 2016-08-18 | TRUE |
I want to add a boolean column to the Customer Contact Table that will show if any purchases for the product and customer occurred less than ONE MONTH after the Customer Contact Date. (any number of purchases might have been made before, during or after that time frame)
I put in a sample of what the boolean column SHOULD calculate to, but I cannot figure out how to write a measure for this in DAX or create a column for it in Power Query.
Any suggestions?
THANKS!
Solved! Go to Solution.
Thank you. I will try that.
In the meantime, I have figured out a formula that seems to work if I add a column to my contacts table. It seems pretty long and complicated but it is giving the results I expected. The MERGED column in both files is a column I added that combines Customer ID with Product ID (so I guess this is the equivalent of the customer dimension table you suggested)
Contact Influenced Purchase =
If( CALCULATE( COUNTROWS ( Purchases ), Filter ( Filter( FILTER ( ALL ( Purchases ), Contacts[Merged]=Purchases[Merged]), (Purchases[Purchase Date]-Contacts[Contact Date]>= 0)), (Purchases[Purchase Date]-Contacts[Contact Date]<=31)))>0, True, False)
Thanks so much for your help
hi, try with this measure:
flag = VAR contactdate = MIN ( Table2[Contact Date] ) VAR purchasedate = MIN ( Table1[Purchase Date] ) RETURN IF ( purchasedate > contactdate, IF ( DATEDIFF ( contactdate, purchasedate, DAY ) > 30, FALSE (), TRUE () ) )
Thanks - this looks good - but I can't make it work for me as there are multiple purchases before and after the contact date. Since both tables have multiple records with the same client number and product type, I cannot build a relationship between the tables (or maybe I can but I don't know how to make a many to many)
For example If the first Purchase (min purchase date) was in 2015 and the first contact date (min contact date) was in late Feb 2016, and there was another purchase in early March 2016, then the answer should be TRUE but I using this calculation I get FALSE.
Is there something I am doing wrong? Perhaps, instead of a measure, I should be making a column - but I still don't know how I would do that.
Here is a link to the testfile that I created in PowerBI which shows the results of the flag formula https://www.dropbox.com/s/r8fqn2trb7ndzvf/TestCase.pbix?dl=0
create a table with customers and related to both tables.
You can do it this in Modeling Menu, New Table
=Distinct(Table2[Customer])
Thank you. I will try that.
In the meantime, I have figured out a formula that seems to work if I add a column to my contacts table. It seems pretty long and complicated but it is giving the results I expected. The MERGED column in both files is a column I added that combines Customer ID with Product ID (so I guess this is the equivalent of the customer dimension table you suggested)
Contact Influenced Purchase =
If( CALCULATE( COUNTROWS ( Purchases ), Filter ( Filter( FILTER ( ALL ( Purchases ), Contacts[Merged]=Purchases[Merged]), (Purchases[Purchase Date]-Contacts[Contact Date]>= 0)), (Purchases[Purchase Date]-Contacts[Contact Date]<=31)))>0, True, False)
Thanks so much for your help
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |