Forum Discussion
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 associated with closest First_Payment_Date which is NOT AFTER that Date
| Table 1: Inquiries | ||||
| Created_On | Subject | Medium | Campaign | Client_ID |
| 1/1/2015 | Africa | Hubspot | 12345 | |
| 3/1/2015 | India | Discount | 12345 | |
| 4/1/2015 | Kenya | Giraffe | 12345 | |
| 7/1/2017 | Tanzania | Zebra | 12345 | |
| 8/1/2017 | Botswana | Thanksgiving | 12345 |
| Table 2: Guests | ||
| Client_ID | Booking_Name | First_Payment_Date |
| 12345 | Clients_First_Booking | 6/1/2015 |
| 12345 | Clients_Second_Booking | 10/1/2017 |
| Goal Result Table | ||||||
| Created_On | Subject | Medium | Campaign | Client_ID | Booking_Name | First_Payment_Date |
| 1/1/2015 | Africa | Hubspot | 12345 | Clients_First_Booking | 6/1/2015 | |
| 3/1/2015 | India | Discount | 12345 | Clients_First_Booking | 6/1/2015 | |
| 4/1/2015 | Kenya | Giraffe | 12345 | Clients_First_Booking | 6/1/2015 | |
| 7/1/2017 | Tanzania | Zebra | 12345 | Clients_Second_Booking | 10/1/2017 | |
| 8/1/2017 | Botswana | Thanksgiving | 12345 | Clients_Second_Booking | 10/1/2017 |
Many thanks for any help in doing this!
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])
7 Replies
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 - you gave me exactly what I asked, thank you, and I marked it as a solution.
I realize though that I need to take it a step more. I believe I need a measure but I'm a little stuck as to what it is.
The image below is using Inquires[Booking_Name} and Charting it under other Inquiries fields.
The three lines with the blue dots are the same Inquiries[Booking_Name] line because it is associated with 3 different Inquiries.
I would like it only to highlight the one with the earliest Inquiries [Created Date] (not used in the graphic). Can you help me here?
could you pls share the pbix file after removing the sensitive data?
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.
