cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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
Highlighted
Super User IV
Super User IV

Re: Request for DAX/Calculate coaching - lookup across tables

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





Highlighted
Super User IV
Super User IV

Re: Request for DAX/Calculate coaching - lookup across tables

@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!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Request for DAX/Calculate coaching - lookup across tables

@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

Highlighted
Helper II
Helper II

Re: Request for DAX/Calculate coaching - lookup across tables

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 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors