using Dax Formula for an entire Table?

[dw700d]

Good day, I have a data set that includes 45 different questions with Yes, No and N/A answers. I used the formula below to calculate how many times a question was answered with a Yes, No or N/A. I would like to avoid having to enter these formulas for the other 44 questions in my data set. That would take all day 😞 . Is there a way to apply these formulas to my entire table? I have also included a sample data set below. Thanks for your help

Yes = CALCULATE(count(Sheet1[Column1]),Sheet1[Column1] = "yes"

No = CALCULATE(count(Sheet1[Column1]),Sheet1[Column1] = "No"

N/A = CALCULATE(count(Sheet1[Column1]),Sheet1[Column1] = "N/A"

Student	Site	Date	Is the Ball White	Is the Ball Blue	Ist the ball Brown	Is the Ball red	Is the ball pink	Is the Ball Yellow
1	Jamaica	12/1/2018	Yes	Yes	Yes	No	Yes	Yes
2	Columbia	12/3/2018	Yes	Yes	Yes	No	No	No
54	Mexico	11/1/2018	NO	Yes	Yes	Yes	No	No
4	Newyork	11/4/2018	no	Yes	Yes	Yes	No	No
5	Jamaica	12/3/2018	N/A	Yes	No	Yes	No	No
43	Columbia	11/1/2018	N/A	Yes	Yes	Yes	No	Yes
32	Mexico	11/4/2018	no	Yes	Yes	No	No	No
9	Newyork	11/1/2018	N/A	Yes	Yes	Yes	No	No
12	Alabama	11/4/2018	no	Yes	N/A	Yes	No	Yes
23	Columbia	12/1/2018	Yes	Yes	Yes	Yes	No	No
56	Jamaica	12/3/2018	Yes	Yes	Yes	Yes	No	No
75	Bogota	11/1/2018	Yes	Yes	Yes	Yes	No	Yes
432	Bogota	11/4/2018	Yes	Yes	Yes	Yes	No	Yes

[LivioLanzo · Accepted solution]

Hi @dw700d,

you can apply a transformation to your data which unpivots the questions columns, you can do it with this Power Query:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzE3MTE4EsgyN9A31jQwMLYDsyNRiNNIvH0UgVidayQjIds7PKc1NyoTqNyagH0aAdJuaANm+qRWZySBBQ0OE5WhWoZsA0gzS65daXplflA3RbEKCZlN0b8Od7afviM3DKDYbo/ka2eFI2rGGHcgAYyM0f+N2OkaYWaJ7mziroboNQTY75iQmAf2O22qsYQCObnSf400vWILdDGe4E6PdHBRtTvnp+SXowY5XN8z1JuBwR9ZvQrz+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Student = _t, Site = _t, Date = _t, #"Is the Ball White" = _t, #"Is the Ball Blue" = _t, #"Ist the ball Brown" = _t, #"Is the Ball red" = _t, #"Is the ball pink" = _t, #"Is the Ball Yellow" = _t]),

ChangedType = Table.TransformColumnTypes(Source,{{"Student", Int64.Type}, {"Site", type text}, {"Date", type date}, {"Is the Ball White", type text}, {"Is the Ball Blue", type text}, {"Ist the ball Brown", type text}, {"Is the Ball red", type text}, {"Is the ball pink", type text}, {"Is the Ball Yellow", type text}}),

UnpivotedOtherColumns = Table.UnpivotOtherColumns(ChangedType, {"Student", "Site", "Date"}, "Question", "Response")

in

UnpivotedOtherColumns

then one measure does it all: NumberOfResponses = COUNTROWS( Data ) drop the questions on the matrix rows and the response on the matrix column

[LivioLanzo]

sorry but not sure why the posts suddenly get pasted in such a bad format