Calculating from Columns in Different Tables

[reast]

We have two screening tools and together, they are used to determine a client's level of care. Screening A is just one question with one score. Screening B has multiple questions and the sum, as well as the answer to one question in particular (weekly care), is used in conjunction with Screening A to determine the level of care. I haven't been able to use RELATED because they are only indirectly connected through my two tables (listed below) because it needs to be a many to one link. I was able to solve it in Access Database query, but my DAX is not as good so I'm struggling to figure out how to link Screening A to B on Client ID and Date so I can use the Screening A Score, Screening B Sum of Scores, and Screening B WeekCare to determine the level of care for each month. Any help would be greatly appreciated. Due to HIPAA, I can not post the actual data.

Screening A:

Client ID, Date, Score

Screening B:

Client ID, Date, Question, Score, Sum of Scores (measure), WeekCare

Table A:

Client ID

Table B:

Calendar

[Phil_Seamark]

HI @reast

You could create a table that merges the two screening tables together.  This can be achieved both in the Query Editor as well as in DAX.  Do you have a preference for which (I assume you are importing data).

How big are the tables?

[reast]

Right now the largest table has 66,000 rows. I highlighted the client ID on the two tables to merge and it just kept saying estimating matches and wouldn't let me click OK. I'm guessing it's too big.

[v-yuezhe-msft]

@reast,

Could you please share sample data of the above tables and post expected result in table format? Also please help to post a snapshot about relationships among these tables.

Regards,
Lydia

[reast]

I was able to get the two screening tools into the same table and calculate the level of care. I was able to determine an average level of care over the past 6 months using a measure. However, I want to use that average level of care per client to indicate clinician availability. A clinician with 6 clients at a high level of care has less room than a clinician with 6 clients at a lower level of care. So I need the level of care average to be fixed to the client. However, since it's a measure, it calculates in the moment, and works to show in a table on a dashboard, but I can't use it to make further calculations (i.e. totaling a clinicians weighted caseload). Should I post this as a new issue since it's different now?

[v-yuezhe-msft]

@reast,

Please share sample data of your tables and post new expected result here.

Regards,
Lydia

[reast]

Screening:

Date                Client ID              LOC     Clinician

4-1-17             1234                   2                A

5-5-17             1234                   3                A

6-4-17             7890                   4               A

4-6-17             7890                   5               A

5-8-17             5678                   1               B

9-3-17             5678                   3               B

Table using LOC Average Measure on Dashboard shows:

1234              2.5

7890              4.5

5678               2

What I want to do then is use the average LOC per client and then add them together to get a weighted caseload for each clinician.

Clinician A        7

Clinician B        2

Thanks for your help!

[Phil_Seamark · Accepted solution]

Hi @reast

I think I see what you needed.  I think this calculated table is close

New Calcuated Table = 
VAR Group1 = SUMMARIZECOLUMNS('Screening'[Client ID] , "Average Weight" , AVERAGE('Screening'[LOC]))
VAR Group2 = SUMMARIZECOLUMNS('Screening'[Client ID],'Screening'[Clinician])
VAR Group3 = GROUPBY(
                        NATURALINNERJOIN(Group1,Group2) ,
                            'Screening'[Clinician],"Weighted Caseload",
                            SUMX(CURRENTGROUP(),
                                [Average Weight]
                                )
                         )
RETURN Group3

[reast]

That's perfect!! Thank you so much! Happy Thanksgiving!!

[Phil_Seamark]

Hi @reast

What formula are you using to determin that Clinician A is 7 and Clinician B is 2?