Forum Discussion
Calculating from Columns in Different Tables
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
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,
LydiaI 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?
