Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JDBOS
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

 

 

4 REPLIES 4
amitchandak
Super User
Super User

@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

@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 

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.