Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Get a table with comments

Please help to get a table that displays comments to questions for which the user has chosen the answer "not ok"

The tables are linked by the following fields:
FormFilled[id] - FormsQuestionsSelectedAnswers[FormFillidid] and
FormQuestion[id] - FormsQuestionsSelectedAnswers[Questionid]

FormsQuestionSelectedAnswers

 

FormQuestion

 

FormFilled

 

 

 

Need Help
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi,

    I see, in that case something like this should work:

    data:


    Dax:

    Measure  11= IF(contains(filter(all(Questions),Questions[QID]=MAX(Questions[QID])),Questions[Comment],"Not ok"),1,0)

    End result:

     



5 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,

    I assume your end goals is to have a visual of sorts and you don't want to have an actual calculated table? If this is the case, you can achieve this by using a visual level filter Table[Answer]="not ok" or by creating a filter measure with the same logic. Filter Measure = IF(FormsQuestionSelectedAnswers[Answer]="not ok",1,0) ->apply this to a visual where [Filter Measure] is 1. If you want to have and calculated table with the comments ping me with @.

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!


  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, ValtteriN


    Yes, I want to creat visual element

    I can't use this measure because PBI throws the following error:

    Each row in the FormFilled table is information about the completed form. Each form has a set of questions and comments that reside in the FormQuestion. FormsQuestionSelectedAnswers contains answers to form questions, standard ones that the user can select (ok, not ok, N/A) and entered by the user himself in a free form - these are comments. In my understanding, the difficulty is that the wording of the questions and the names of the fields for comments are in the same column of the FormQuestion table.

    The database logic is built in such a way that for it there is no difference whether it is a comment or a question - for it these are all questions with different id. I can't just select the "not ok" filter on a visual and get a list of comments. They have a different id and the visual element will display only questions to which the user answered "not ok".

    • ValtteriN's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi,

      You can use MAX('Table'[column])="not ok" to solve this error.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        ValtteriN  MAX() - is work, but this is not the answer to my question. I need to display comments for questions that are "not ok", not only the questions themselves. Sorry, for the russian words. If I use your measure I will see Title contains only questions. 

         

        I want to get visal like this:

        Each filled form has its own FormFilled id. Question id Questions from the FormQuestions table are attached to it. 1654 - comment (also called in Russian) 1653 - a question to which the answer "not ok" was received. I want to leave only the lines with questions that are answered with "not ok" and that also have a comment filled in. The number at the beginning of the wording of the question and the comment is the link between them. If the question starts with the number 9, then the comment on this question also starts with the number 9