Forum Discussion
Add a column from a different query for which the value is determined by both a match and a date
Hi,
I have 2 different tables, each in a different query.
1. A table that defines the date a time for which some events (Adverse Events) occured to some people (Subject) from different group populations (Study).
2. A table that defines the date and time for which a important procedure (Dosing) was collected on each "Subject". The "Dosing" can be performed multiple time for a given "Subject"
I want to add 2 columns to the first table. The column should look for the "No" in the column "Is this a Pre-dose AE?", then look for a match with columns "Study" and "Subject", and finally select the data from the appropriate row from the "Dosing" table according to the date and time of the "Adverse Events"
1. A column that wil indicate the date and time of the first "Dosing" for that subject.
2. A column that wil indicate the date and time of the latest "Dosing" for that subject. So if an "Adverse Event" started between the 2nd and 3rd "Dosing" for that subject, the data should reflect the date and time of the 2nd "Dosing" for this subject.
Here's a sample of the tables I have, with the added "Expected results" table:
Table 1: Adverse Event
| Study | Subject | Adverse Event Title | Start Time | Is this a Pre-dose AE? |
| A | 1 | AE 01 | 10/19/2025 19:00 | Yes |
| A | 1 | AE 02 | 10/21/2025 8:30 | No |
| A | 1 | AE 03 | 10/22/2025 9:15 | No |
| A | 2 | AE 01 | 10/22/2025 13:00 | No |
| B | 1 | AE 01 | 10/10/2025 8:00 | No |
| B | 2 | AE 02 | 10/17/2025 8:00 | No |
| C | 1 | AE 01 | 10/23/2025 8:00 | No |
Table 2: Dosing
| Study | Subject | Period | Dose Number | Scheduled Timepoint | Collection Time |
| A | 1 | 1 | 1 | Day 1 | 10/20/2025 8:00 |
| A | 1 | 1 | 2 | Day 2 | 10/21/2025 8:00 |
| A | 1 | 1 | 3 | Day 3 | 10/22/2025 8:00 |
| A | 2 | 1 | 1 | Day 1 | 10/20/2025 8:10 |
| A | 2 | 1 | 2 | Day 2 | 10/21/2025 8:10 |
| A | 2 | 1 | 3 | Day 3 | 10/22/2025 8:10 |
| B | 1 | 1 | 1 | Day 1 | 10/10/2025 8:00 |
| B | 1 | 2 | 2 | Day 1 | 10/17/2025 8:00 |
| C | 1 | 1 | 1 | Day 1 | 10/20/2025 8:00 |
Expected Result:
| Study | Subject | Adverse Event Title | Start Time | Is this a Pre-dose AE? | First Dosing Date/Time | Latest dosing Date/Time |
| A | 1 | AE 01 | 10/19/2025 19:00 | Yes | null | null |
| A | 1 | AE 02 | 10/21/2025 8:30 | No | 10/20/2025 8:00 | 10/21/2025 8:00 |
| A | 1 | AE 03 | 10/22/2025 9:15 | No | 10/20/2025 8:00 | 10/22/2025 8:00 |
| A | 2 | AE 01 | 10/22/2025 13:00 | No | 10/20/2025 8:10 | 10/22/2025 8:10 |
| B | 1 | AE 01 | 10/10/2025 8:00 | No | 10/10/2025 8:00 | 10/10/2025 8:00 |
| B | 1 | AE 02 | 10/17/2025 8:00 | No | 10/10/2025 8:00 | 10/17/2025 8:00 |
| C | 1 | AE 01 | 10/23/2025 8:00 | No | 10/20/2025 8:00 | 10/20/2025 8:00 |
My goal is ultimately to be able to determine if there is a recurence in events and to determine of the events all occur approximately around the same time. Once these columns are created I'll be able to calculate the duration I need to include in my visuals.
The tables will grow with time, so I need a formula that will take into account the new entries that will be entered (additional studies and subjects).
Thank you all for your support!
Christian
Hi Christian,
Not sure if you want Power Query or DAX or what the relationships in your model look like, but as a first draft, DAX, try this...
First Dosing for Subject = CALCULATE( MIN(Dosing[Collection Time]), FILTER( Dosing, Dosing[Study] = 'Adverse Event'[Study] && Dosing[Subject] = 'Adverse Event'[Subject] && 'Adverse Event'[Is this a Pre-dose AE?] = "No" ) )Last Dosing Before AE = CALCULATE( MAX(Dosing[Collection Time]), FILTER( Dosing, Dosing[Study] = 'Adverse Event'[Study] && Dosing[Subject] = 'Adverse Event'[Subject] && Dosing[Collection Time] <= 'Adverse Event'[Start Time] && 'Adverse Event'[Is this a Pre-dose AE?] = "No" ) )This was done quickly, may need to tweak the calculation a bit.
2 Replies
- KNP
Super User
Hi Christian,
Not sure if you want Power Query or DAX or what the relationships in your model look like, but as a first draft, DAX, try this...
First Dosing for Subject = CALCULATE( MIN(Dosing[Collection Time]), FILTER( Dosing, Dosing[Study] = 'Adverse Event'[Study] && Dosing[Subject] = 'Adverse Event'[Subject] && 'Adverse Event'[Is this a Pre-dose AE?] = "No" ) )Last Dosing Before AE = CALCULATE( MAX(Dosing[Collection Time]), FILTER( Dosing, Dosing[Study] = 'Adverse Event'[Study] && Dosing[Subject] = 'Adverse Event'[Subject] && Dosing[Collection Time] <= 'Adverse Event'[Start Time] && 'Adverse Event'[Is this a Pre-dose AE?] = "No" ) )This was done quickly, may need to tweak the calculation a bit.
It worked! Thank you!
