Forum Discussion
Referencing a Survey from Column names as Variables
I have a survey in a table that has almost a hundred columns wide.
I am trying to find the number of "No" results in each column and sort these as a table to find the most common "No" answers
I attempted to do this by creating a new table called FieldNames that had the column names.
--
NoCount =
Var ColumnName ='FieldNames'[Title]
VAR TheseCols = SELECTCOLUMNS('SurveyTable',"ThisCol",ColumnName)
VAR Result = countrows(filter(TheseCols, [ThisCol]="No"))
Return Result
--
But the SelectColumns only returns a column filled with the name of the column repeated endlessly. This seems like something that should be easy, just referencing a column name from a variable, but every solution I have seen requires explicitly combining the columns in the code, which isn't feasible on something this size.
Any ideas would be appreciated.
Hi,
I'd use Power Query first to reshape the data a bit for this type of tasks. Since your survey is about 100 columns wide and all answers are similar like Yes/No. In Power Query, your can select one identifier columns such as RespondentID, Date, etc. and unpivot other Columns, and you will end up with Question and Answer with Yes/No.
Then in reporting use a DAX Meaure for calculation.
Measure = CALCULATE ( COUNTROWS ( 'table' ), 'table'[Answer] = "No" )
2 Replies
- MasonMA
Super User
Hi,
I'd use Power Query first to reshape the data a bit for this type of tasks. Since your survey is about 100 columns wide and all answers are similar like Yes/No. In Power Query, your can select one identifier columns such as RespondentID, Date, etc. and unpivot other Columns, and you will end up with Question and Answer with Yes/No.
Then in reporting use a DAX Meaure for calculation.
Measure = CALCULATE ( COUNTROWS ( 'table' ), 'table'[Answer] = "No" )
- cengizhanarslan
In DAX you can’t dynamically reference a physical column by name (strings don’t become column references). That’s why your SELECTCOLUMNS just repeats the text.
The practical solution is to reshape the survey to a long format once, then counting “No” becomes trivial.
Power Query approach:
In Power Query select your respondent ID columns (e.g., ResponseID, Date, etc.)
Transform → Unpivot Other Columns
You’ll get:
Question (former column name)
Answer (value)
Then a simple measure:
No Count = CALCULATE ( COUNTROWS ( SurveyLong ), SurveyLong[Answer] = "No" )Put SurveyLong[Question] in a table visual with No Count, sort descending.
