I'm new to DAX and work with medical data. The Diagnosis Table / Observation Values (OBSV) column, can be alpha & numeric (as in a blood test score, blood pressure reading, behavoral health score or 12,>16, 3/4, 02/26/2021.
I need to create 3 measures that will:
1) SUM all the scores for each patient, being sure to filter the OBS Name column for GAD&SCORE and PHQ (see the pic)
2) Average those scores ( that will be another column
3) Then based on the scores, determine the risk factor of low, medium or high ( have these values, only need the synax structure)
Thanks but I cannot get the result I seek from the above ideas so let me try & give more info. I have a table of medical observation names which I have filtered for GAD7SCORE and PHQ-9.
I would like to create 2 measures that will give me the totals of all scores and the average of those scores. My first issue is that the OBS Value column holds string data (alpha and numeric values) so when I try to create a new column using -
Total Score = SUM(MERGE_Observations[OBSVALUE]), I get the error 'function SUM cannot work with values of type string'.
Since I am already filtering the OBS Name for just GAD7SCORE and PHQ-9, I was hoping to just SUM those actual numeric scores.
The end goal is to have my visual look like below, with the 2 additional columns.
1 - A Total of all the scores shown
(turning on sub totals works per year which is not helpful)
2 - The average which is calculted by the total score devided by the number of test so here the PHQ was given 4 times so 31/4=7.5
I will then use conditional formatting to create a flag indicating the high, medium and low risk factors based on the average score. Below is a visual example of what I want in the end however the calculations in this report are not correct