cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anwilkins
Frequent Visitor

Filter then sum an alpha / numeric column

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 for any insight!

 

anwilkins_0-1625059463892.png

 

1 ACCEPTED SOLUTION
anwilkins
Frequent Visitor

Found a fix. Created a calculated column to pull over only the numeric values. there were still entries for a 0, and for Ne that were then changed to Null. Those values are being ignored.

 

#"Added Custom1" = Table.AddColumn(#"Removed Columns2", "OBSV", each if Value.Is(Value.FromText([OBSVALUE]), type number) then [#"OBSVALUE"] else null),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"OBSV", Int64.Type}})

View solution in original post

3 REPLIES 3
anwilkins
Frequent Visitor

Found a fix. Created a calculated column to pull over only the numeric values. there were still entries for a 0, and for Ne that were then changed to Null. Those values are being ignored.

 

#"Added Custom1" = Table.AddColumn(#"Removed Columns2", "OBSV", each if Value.Is(Value.FromText([OBSVALUE]), type number) then [#"OBSVALUE"] else null),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"OBSV", Int64.Type}})

View solution in original post

v-xiaotang
Community Support
Community Support

Hi @anwilkins 

1) SUM all the scores for each patient, being sure to filter the OBS Name column for GAD&SCORE and PHQ (see the pic)

-
three methods:
1. if you've selected GAD&SCORE and PHQ by filter, you can use fuction Sum() & ALLSELECTED(), FYI:

https://docs.microsoft.com/en-us/dax/allselected-function-dax

2. otherwise, use function Sum() & Filter(), e.g. filter(all('table'),'table'[OBS NAME] in {"GAD","SCORE","PHQ"})
3. go to Format tab - Subtotals -

vxiaotang_0-1625209590987.png

 

2) Average those scores ( that will be another column

-

What is the denominator? Is all the date, even if there is no value, or only the date with value? By the way, it's impossible to put it into the matrix because the matrix already has the total column.

 

3) Then based on the scores, determine the risk factor of low, medium or high ( have these values, only need the synax structure)

-

not very clear about the result you want...

 

If you need more detailed measure expressions, could you share your PBI file after removing sensitive information?

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

 

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. 

anwilkins_0-1625591741749.png

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)

anwilkins_0-1625593798397.png

 

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 

anwilkins_1-1625592078677.png

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

anwilkins_1-1625593870847.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!