Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Request for DAX/Calculate coaching - lookup across tables

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

JFCS SFDC AFC team data relationships 6.1b 2020-04-27 recurring docs w formatting.jpg


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!


JFCS SFDC AFC team data relationships 2.0a 2020-04-27 all data tables.jpg



Super User IV
Super User IV

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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 

JFCS SFDC AFC team data relationships 1.0a 2020-04-27 PBI Model.jpg

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!


JFCS SFDC AFC team data relationships 7.1a 2020-05-06 recurring docs w formatting.jpg

@amitchandak @parry2k 

Super User III
Super User III

@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.

Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors