cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Calculating from Columns in Different Tables

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

1 ACCEPTED SOLUTION
Microsoft Employee

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) ,
SUMX(CURRENTGROUP(),
[Average Weight]
)
)
RETURN Group3```

Proud to be a Datanaut!

9 REPLIES 9
Microsoft Employee

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?

Proud to be a Datanaut!

Helper II

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.

Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

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?

Microsoft Employee

@reast,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

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

Microsoft Employee

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) ,
SUMX(CURRENTGROUP(),
[Average Weight]
)
)
RETURN Group3```

Proud to be a Datanaut!

Helper II

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

Microsoft Employee

Hi @reast

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

Proud to be a Datanaut!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.