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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rloski-public
Helper I
Helper I

Display two fact tables that are related by one dimension but differing times

I have two fact tables:

 

Emails sent

Email responses

 

Emails Sent

ContactCustomerDate Sent
Russ's idCustomer1's idJuly 7, 2022
John's idCustomer2's idJuly 7, 2022
Russ's idCustomer1's idJuly 15, 2022

 

Email Responses

ContactCustomerResponse DateNote
Russ's idCustomer1's IDJuly 10, 2022I looked at the record
N/ACustomer2's IDJuly 11, 2022Called customer
Russ's idCustomer1's IDJuly 12, 2022Left voicemail
Steve's idCustomer1's IDJuly 13, 2022Resent email

 

The results that I would like are the following (actually, I would have values from the Contact and Customer Dimensions):

ContactCustomerDate SentResponse dateNote
Russ's idCustomer1's idJuly 7, 2022July 10, 2022I looked at the record
Russ's idCustomer1's idJuly 7, 2022July 13, 2022Resent email
Russ's idCustomer1's idJuly 7, 2022July 12, 2022Left voicemail
John's idCustomer2's idJuly 7, 2022July 11, 2022Called customer 
Russ's idCustomer1's idJuly 15, 2022  

 

There is nothing in the source to identify the emails sent that the email response is to.  I am assuming that if there is an email response after an email is sent for a customer or contact, then assign it to the previous email send record.

Here's the rules (as I have thought them through so far):

  • Each email response is assigned to zero or one email send, the one that was the last email sent before the response.
  • The customer on the email send must match the customer in the email response.
  • If the contact is not N/A and matches the contact for a recent email send, then associate the response to the email send
  • If the contact is N/A in the response, then assoicate the email response to the most recent email send for the customer, assigning it to multiple contacts.
  • If the most recent email for the customer is not associated with the contact in the email response, then treat the contact as if it is N/A and associate the response to all of the emails that were sent to that company.

I am not sure the best way to model this.

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

Conflate both tables into one. 

Contact|Customer|Date|Sent or Received|Note

 

That will make the rest of the process a bit easier.

 

sort the table by Customer, Contact, Date. Add an index column for better lookup performance.

View solution in original post

v-yangliu-msft
Community Support
Community Support

Hi  @rloski-public ,

Here are the steps you can follow:

1. Create calculated table.

True =
var _table1=
SUMMARIZE('Email Responses', 'Email Responses'[Contact],'Email Responses'[Customer],'Email Responses'[Response Date],
"Date Sent",CALCULATE(MIN('Emails Sent'[Date Sent]),FILTER(ALL('Emails Sent'),
'Emails Sent'[Customer]='Email Responses'[Customer])))
var _table2=
SUMMARIZE(_table1,[Contact],[Customer],[Date Sent],[Response Date])
var _table3=
FILTER(ALL('Emails Sent'),'Emails Sent'[Date Sent]=MAX('Emails Sent'[Date Sent]))
var _table4=
ADDCOLUMNS(_table3,"Response date",BLANK())
var _table5=
UNION(_table2,_table4)
return
_table5

2. Create calculated column.

Note =
MAXX(FILTER(ALL('Email Responses'),
'Email Responses'[Response Date]='True'[Response Date]),[Note])

Contact_True =
SWITCH(
    TRUE(),
    'True'[Contact]<>"N/A"&&'True'[Response Date]=BLANK(),CALCULATE(MAX('True'[Contact]),FILTER(ALL('True'),
    'True'[Customer]=EARLIER('True'[Customer])&&
    'True'[Response Date]=MinX(FILTER(ALL('True'),[Customer]=EARLIER('True'[Customer])),[Response Date]))),
    'True'[Contact]<>"N/A",
    CALCULATE(MAX('True'[Contact]),FILTER(ALL('True'),
    'True'[Customer]=EARLIER('True'[Customer])&&
    'True'[Response Date]<EARLIER('True'[Response Date]))),
    'True'[Contact]="N/A",
    CALCULATE(MAX('Emails Sent'[Contact]),FILTER(ALL('Emails Sent'),'Emails Sent'[Customer]='True'[Customer])
    ))

3. Create calculated table.

True_Table =
SUMMARIZE('True',
[Contact_True],[Customer],[Date Sent],[Response Date],[Note])

4. Result:

vyangliumsft_0-1659442025456.png

If you need pbix, please click here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @rloski-public ,

Here are the steps you can follow:

1. Create calculated table.

True =
var _table1=
SUMMARIZE('Email Responses', 'Email Responses'[Contact],'Email Responses'[Customer],'Email Responses'[Response Date],
"Date Sent",CALCULATE(MIN('Emails Sent'[Date Sent]),FILTER(ALL('Emails Sent'),
'Emails Sent'[Customer]='Email Responses'[Customer])))
var _table2=
SUMMARIZE(_table1,[Contact],[Customer],[Date Sent],[Response Date])
var _table3=
FILTER(ALL('Emails Sent'),'Emails Sent'[Date Sent]=MAX('Emails Sent'[Date Sent]))
var _table4=
ADDCOLUMNS(_table3,"Response date",BLANK())
var _table5=
UNION(_table2,_table4)
return
_table5

2. Create calculated column.

Note =
MAXX(FILTER(ALL('Email Responses'),
'Email Responses'[Response Date]='True'[Response Date]),[Note])

Contact_True =
SWITCH(
    TRUE(),
    'True'[Contact]<>"N/A"&&'True'[Response Date]=BLANK(),CALCULATE(MAX('True'[Contact]),FILTER(ALL('True'),
    'True'[Customer]=EARLIER('True'[Customer])&&
    'True'[Response Date]=MinX(FILTER(ALL('True'),[Customer]=EARLIER('True'[Customer])),[Response Date]))),
    'True'[Contact]<>"N/A",
    CALCULATE(MAX('True'[Contact]),FILTER(ALL('True'),
    'True'[Customer]=EARLIER('True'[Customer])&&
    'True'[Response Date]<EARLIER('True'[Response Date]))),
    'True'[Contact]="N/A",
    CALCULATE(MAX('Emails Sent'[Contact]),FILTER(ALL('Emails Sent'),'Emails Sent'[Customer]='True'[Customer])
    ))

3. Create calculated table.

True_Table =
SUMMARIZE('True',
[Contact_True],[Customer],[Date Sent],[Response Date],[Note])

4. Result:

vyangliumsft_0-1659442025456.png

If you need pbix, please click here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

I am going to look at how this works and whether I can use this in another solution.

 

It might use a little more space than the other approach, though my tables are under about 3,000 rows.

I will look at this solution later today.  It addresses several major questions I have about this situation that I need to learn.

lbendlin
Super User
Super User

Conflate both tables into one. 

Contact|Customer|Date|Sent or Received|Note

 

That will make the rest of the process a bit easier.

 

sort the table by Customer, Contact, Date. Add an index column for better lookup performance.

What I ended up doing was to create a calculated column that had the identifier (which I left out of my sample) for the row that I needed.  I chose to simplify for my first pass to get the first row by date for the customer.   I then created a relationship between the tables on that column That addresses at least 80 % of the cases.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.