Request for DAX/Calculate coaching - lookup across tables

[JDBOS]

We've made good progress on a status report for tracking the documents needed for clients served by a community-based nonprofit.

But now realize that we need to add a field that isn't part of the initial data table (see table 1 in the following screenshot) Specifically the name of the Nurse (table 4) assigned to the Case Team (table 3) supporting the client's case (table 2)

We're assuming there's a Calculate DAX formula and are hoping to get your help with the syntax.

Thanks in advance for your coaching!

[amitchandak]

@JDBOS , How Id value related to Nurse value. If that can identify it uniquely. You can move it to the ID dimension. Assuming you have a common ID dimension

[JDBOS]

@amitchandak @parry2k 

Here's the data model in PBI Desktop

Let me know if this is helpful - a case can have several team members and Staff can be assigned to many Svc Team Mbr roles 

There should only be one Document Status Tracker per Case Record 

[JDBOS]

As an update, we added automation to update the Names in the CM (Case Manager) and RN ( Registered Nurse) fields in the Document Status Tracker table, but I'd still prefer a lookup that would directly identify:

For the 'Case Record'[ID] referenced by the 'Document Status Tracker'[Case Record]

What is the "first" 'User"[Name]

referenced as 'Svc Team Mbr'[staff] for the first 'Service Team Member'[Role] = "RN" associated with the 'Case Record'[ID]    {see the relationships in the prior response}

Any suggestions would be much appreciated!

@amitchandak @parry2k 

[parry2k]

@JDBOS I guess you want to bring Table 4 in the data model and get Nurse info from this table. Can you share the relationship diagram from your existing model to further understand the model and provide proper solution.