DAX: Return value if date from one table is between two date fields in another table

Hi Power BI friends,

I have three excel tables in my Power BI report:

• ‘DC to Home’
• ‘PC’
• ‘Member ID’

Both tables have the field [Member ID] as the Primary Key.  The ‘Member ID’ table only has the [Member ID] field.

The ‘DC to Home’ table has two date fields:

• [NextAdmit]
• [Discharge Date]

The ‘PC’ table has one date field:

• [Qstn Resp Date]

I would like to create a measure that essentially does the following:

New Measure = IF’DC to Home’[NextAdmit] is > ‘PC’[QSTN Resp Date] AND ‘DC to Home’[Discharge Date] < ‘PC’[QSTN Resp Date] THEN … return the ‘PC’[QSTN Resp Date] … for the ‘Member ID’[Member_ID] ELSE “N/A”.

Here is a visual of the relationship map:

Here is an example of ‘DC to Home’:

Member ID         NextAdmit        Discharge Date

0001                     9/1/2020          9/1/2020

0002                     7/7/2020          7/5/2020

0003                     5/3/2020          4/1/2020

Here is an example of ‘PC’:

Member ID         Qstn Resp Date       

0001                               

0002                     7/6/2020         

0003                     4/2/2020

If the New Measure was working correctly, I’d expect the results of a table visual to be:

Member ID        New Measure         

0001                   N/A

0002                   7/6/2020

0003                   4/2/2020

Is there anyway to build this out as a measure so I can use it in a table visual?

Thank you!