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 VII
Super User VII

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 IX
Super User IX

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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors