Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anwilkins
Resolver II
Resolver II

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
Resolver II
Resolver II

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
Resolver II
Resolver II

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}})

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.