Forum Discussion

apmulhearn's avatar
apmulhearn
Helper III
5 years ago
Solved

Help with Join with Date-Specific Contingencies

Hello!   I have two tables.   I need to join them for attribution purposees. These are the criteria I need to be true: 1. Inquiries are joined on Client ID between tables 2. Inquiries are asso...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    apmulhearn 

    you can use DAX to create two columns

    Booking_name = 
var _date=MINX(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]>Inquiries[Created_On]),Guests[First_Payment_Date])
return maxx(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]=_date),Guests[Booking_Name])

First_payment_Date = 
var _date=MINX(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]>Inquiries[Created_On]),Guests[First_Payment_Date])
return maxx(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]=_date),Guests[First_Payment_Date])

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

apmulhearn 

why not use the opposite way to search data? searching from Inquiries table instead of searching from guest table.

pls see the attachment below

apmulhearn's avatar
apmulhearn
Helper III
5 years ago

Hi ryan_mayu 

 

Thank you for the suggestion to you Guest instead of Inquiry. Unfortunately, there are (potentially) multiple items on each of those tables. That is, there are multiple Guests assoiciated with a booking, and multiple Inquiries associated with a booking.  

 

I substituted "Campaign" for "Medium" to achieve the second level of drill-down data I need (Medium for me is Marketing Platform, Campaign is Marketing Item.). In doing so, since there were multiple guests on George's 2022 booking, he gets lumped in with two different Marketing Campaigns - he and his wife each clicked one. I want to know which was clicked first and attribute ONLY there.

 

 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    apmulhearn 

    I think I still need you to provide me some dummy data and the expected output.