Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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?
@reast,
Please share sample data of your tables and post new expected result here.
Regards,
Lydia
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!
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
That's perfect!! Thank you so much! Happy Thanksgiving!!
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |