Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jesspiorier
Frequent Visitor

Date Differences between tables with many:many relationships

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

 

Data ModelData Model

3 REPLIES 3
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix .

 

Here is a super-simplified view of what I'm trying to accomplish.

Expected Results.PNG

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.