Forum Discussion
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
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
9 Replies
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?
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.
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
