using Dax Formula for an entire Table?

Question

&nbsp;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&nbsp;to enter these&nbsp;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&nbsp;Yes = CALCULATE(count(Sheet1[Column1]),Sheet1[Column1] = "yes"No = CALCULATE(count(Sheet1[Column1]),Sheet1[Column1] = "No"&nbsp;N/A = CALCULATE(count(Sheet1[Column1]),Sheet1[Column1] = "N/A"&nbsp;StudentSiteDateIs the Ball WhiteIs the Ball BlueIst the ball BrownIs the Ball redIs the ball pinkIs the Ball Yellow1Jamaica12/1/2018YesYesYesNoYesYes2Columbia12/3/2018YesYesYesNoNoNo54Mexico11/1/2018NOYesYesYesNoNo4Newyork11/4/2018noYesYesYesNoNo5Jamaica12/3/2018N/AYesNoYesNoNo43Columbia11/1/2018N/AYesYesYesNoYes32Mexico11/4/2018noYesYesNoNoNo9Newyork11/1/2018N/AYesYesYesNoNo12Alabama11/4/2018noYesN/AYesNoYes23Columbia12/1/2018YesYesYesYesNoNo56Jamaica12/3/2018YesYesYesYesNoNo75Bogota11/1/2018YesYesYesYesNoYes432Bogota11/4/2018YesYesYesYesNoYes

liviolanzo · Accepted Answer

Hi dw700d,you can apply a transformation to your data which unpivots the questions columns, you can do it with this Power Query:&nbsp;&nbsp;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&nbsp;&nbsp;then one measure does it all: NumberOfResponses = COUNTROWS( Data ) drop the questions on the matrix rows and the response on the matrix column

Forum Discussion

using Dax Formula for an entire Table?

2 Replies

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