Retrieve Date from another table if Date field falls between two dates in second table

[AW1976NOVA]

Hi Power BI friends,

My goal is to retrieve 'PC'[Qstn Resp Date] for each [Member ID] if 'DC to Home'[NextAdmit] > 'PC'[Qstn Resp Date] and 'DC to Home'[DischargeDate] < 'PC'[Qstn Resp Date].  Else, return "N/A".

My issue is I do not know how to write this as a measure because the fields are within different tables.

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?  Is there an easier way to build using M code?

Thank you!

[v-zhenbw-msft · Accepted solution]

Hi @AW1976NOVA ,

If you have unique member ID in your DC to home table and PC table like your sample data, we can create four measures to meet your requirement.

Discharge Date Measure = CALCULATE(MAX('DC to Home'[Discharge Date]),FILTER('DC to Home','DC to Home'[Member ID]=MAX('Member ID'[Member ID])))

NextAdmit Measure = CALCULATE(MAX('DC to Home'[NextAdmit]),FILTER('DC to Home','DC to Home'[Member ID]=MAX('Member ID'[Member ID])))

Qstn Resp Date Measure = CALCULATE(MAX(PC[Qstn Resp Date]),FILTER(PC,PC[Member ID]=MAX('Member ID'[Member ID])))

New measure = 
IF(
    [Qstn Resp Date Measure]>[Discharge Date Measure] && [Qstn Resp Date Measure]<[NextAdmit Measure],[Qstn Resp Date Measure],"N/A")

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[lbendlin]

As long as you use the MemberID from the dimension table for your visuals (you should really hide that field in the fact tables) this should be straightforward.  Where exactly are you stuck?