Forum Discussion

apmulhearn's avatar
apmulhearn
Icon for Helper III rankHelper 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])

apmulhearn's avatar
apmulhearn
Icon for Helper III rankHelper III
5 years ago

It is loaded with sensitive data and will take me a bit of time to remove, but you are helping me, so I will gladly do it!

 

In the meantime, though, I think I can exemplify it based on the small sample you helped me with above. If this doesn't help I will circle back asap.


 

 

 

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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
      Icon for Super User rankSuper User
      5 years ago

      apmulhearn 

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