Forum Discussion

Newbie123's avatar
Newbie123
New Member
9 years ago

Groupby on calculated column

I have two tables: one table has columns that show:

  • a QuestionID (unique to the question)
  • an AnswerID (unique to the respondent) and
  • a ChosenAnswerSequence# (that reflects the actual answer the person chose from a select answer set. This is displayed as a number in the table, but the answer is actually text-based).  

The second table has columns that show:

  • the Question ID,
  • a PossibleAnswerSequence# (that reflects the various answer options that are associated with the questions as the question may or may not have multiple answers. This is displayed as a number, but the answer is actually text-based) 
  • the corresponding text-based answer to the PossibleAnswerSequence# .

On the report I want to generate, I want to put the respondent's text-based answers on the same row/line as the question.

 

I have successfully created a calculated column that does a lookupvalue function to bring the chosen text-based answers into the first table. I can also successfully do a query that allows me to combine multiple rows into one value (by using Groupby as well as a custom column formula of =Table.Column([MulticheckboxAnswersGrouped], "Sequence"). However, the glitch is that I can only do the query/custom column if it's the ChosenAnswerSequence# from the first table...I am unable to do this second step with the text-based answers from the calculated column. Are you not able to use the group by function using the results from a calculated column?

Need Help

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Newbie123

     

    In this scenario, each respondent may have multiple selection on a question, to have them appear on same row along with question, you need to use CONCATENATEX() function to concatenate all answers into single string.

     

    Measure =
CALCULATE (
    CONCATENATEX ( Table, Table[Text_BasedAnswer], "," ),
    ALLEXCEPT ( Table, Table[AnswerID] )
)

    Regards,

    • Newbie123's avatar
      Newbie123
      New Member
      9 years ago

      Thanks for the reply, but I get an error saying that the syntax is incorrect. This is what I have written:

       

      Measure = CALCULATE (CONCATENATEX (DFMAnswer_MultipleCheckbox, DFMAnswer_MultipleCheckbox[CheckboxTextAnswer] ","), ALLEXCEPT (DFMAnswer_MultipleCheckbox, DFMAnswer_MultipleCheckbox[AnswerId]))