How to determine multiple values or blanks in a row of data.

[Anonymous]

I have a report that shows the conditions of patients. Every patient will have at least one condition. Some patients have multiple conditions. I need to know the number of patients with multiple conditions (2+). The data looks like the sample below. The highlighted row shows a patient with multiple conditions. I would like a column that either shows the number of conditions a patient has or the number of blanks in the range of fields (five) so I can get a count. This would be a row by row evaluation.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

You can follow the below steps to achieve it, please find the details in the attachment.

1. Select all conditions column and unpivot them first in Power Query Editor just as shown in the below screenshot:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsMTBU0lHyyS8HkggUqwORM8IjZwzk+KamZJbmIjOwCMI0mCAbAzUVJmeKxyIzFFEkCXO4JmStIDZMhQUupyG5LhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Patient = _t, Diabetes = _t, COPD = _t, Asthma = _t, CAD = _t, HF = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient", type text}, {"Diabetes", type text}, {"COPD", type text}, {"Asthma", type text}, {"CAD", type text}, {"HF", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Diabetes", "COPD", "Asthma", "CAD", "HF"}, "Conditions", "Value")
in
    #"Unpivoted Only Selected Columns"

2. Create a measure to get the count of conditions per patient

Count = CALCULATE(COUNT('Table'[Value]),'Table'[Value]<>"")

Best Regards

[HashamNiaz]

Hi @Anonymous !

You can use following DAX to create a custom column which will add non-blank occurences of medical conditions;

Medical Count = IF(NOT(ISBLANK(Table[Diabetes])) && Table[Diabetes] <> "", 1, 0) 
        + IF(NOT(ISBLANK(Table[COPD])) && Table[COPD] <> "", 1, 0)
 + IF(NOT(ISBLANK(Table[Asthma])) && Table[Asthma] <> "", 1, 0)
 + IF(NOT(ISBLANK(Table[CAD])) && Table[CAD] <> "", 1, 0)
 + IF(NOT(ISBLANK(Table[HF])) && Table[HF] <> "", 1, 0)

You can replace Table with your Table Name. The column will add up Non-Blanks for these 5 columns.

Regards,

Hasham