cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Patron

## using Dax Formula for an entire Table?

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
1 ACCEPTED SOLUTION
Solution Sage

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

Proud to be a Datanaut!

2 REPLIES 2
Solution Sage

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

Proud to be a Datanaut!

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

Proud to be a Datanaut!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors