Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need some help with relationship modeling and calculations regarding finding the date difference between events and purchases by customers. The goal is a conversion distance rate for when a customer attended an event and when they made a subsequent purchase.
'Customer Data' has a single entry for each [INDIVIDUAL_ID]
'Events' has many entries for each [INDIVIDUAL_ID]
'A-Purchase' can have many entries for each [INDIVIDUAL_ID] (or none)
'B-Purchase' can have many entries for each [INDIVIDUAL] (or none)
I'm looking to find DATEDIFF from an event ('Events' [LEAD_DATE]) to purchases from either/both purchase tables. I tried creating a table of just distinct Individual IDs to act as a secondary bridge, but my "related" measures gave me errors. I tried using a DATE table, but I couldn't make the relationship active because of cardinality issues. I'm hoping someone smarter than myself can help me figure this out. Thanks in advance. -Jess
Hi @jesspiorier ,
Although youhave place some information in your post by the image you present and without any data is difficult to give you an answer.
Can you please share a sample file and expected result? If the information is sensitive, difficult to make a mockup or you don't want to share it here you can do it trough private message.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix .
Here is a super-simplified view of what I'm trying to accomplish.
Essentially, the number of days between participating in an event/campaign and how long it took them to make a puchase (two different types of purchases, A or B).
All people will have particiapted in at least one Event, will some will have participated in many.
Some people will have made a purchase from A or B (or both) multiple times, and some people will not have purchased anything at all.
I have a customer table with a single, unique [INDIVIDUAL_ID] for each person. I also have a date table. I need a measure or calculated column or something to figure out the DATEDIFF between [LEAD_DATE] to [PURCHASE DATE A] (and/or B). Does that make sense? Is that possible and I'm just over (or under) thinking it? 😆
Hi @jesspiorier ,
Try the following two measures:
To Purchase A =
DATEDIFF (
SELECTEDVALUE ( 'A-PURCHASE'[PURCHASE_DATE] );
SELECTEDVALUE ( EVENT[LEAD_DATE] );
DAY
)
To Purchase B =
DATEDIFF (
SELECTEDVALUE ( 'B-PURCHASE'[PURCHASE_DATE] );
SELECTEDVALUE ( EVENT[LEAD_DATE] );
DAY
)
Check the PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |