Forum Discussion

rkee10's avatar
rkee10
Frequent Visitor
2 years ago
Solved

Grouping of Questions to Create Category

Is it possible to create a report from a list of questions wherein it will group the type of questions as categories?  For example, i have these questions:

 

- What is a guitar

- Guitar

- define guitar

- How to cook meat?

 

3 counts should be under GUITAR query or category and 1 count would be under Cooking.  I hope this is clear.  Let me know otherwise.  Thanks!

  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    2 years ago

    Hi rkee10 

     

    If my understanding is correct then here are the step, to achieve what you have you need to create two custom column

    First column

    NewColumn =
    VAR Sentence = 'Table'[Sentence]
    VAR WordList =
    SELECTCOLUMNS(
    UNION(
    ROW("Word", "guitar"),
    ROW("Word", "cook")
    -- Add more words here as needed
    ),
    "Word", [Word]
    )
    VAR FoundWords =
    FILTER(
    WordList,
    CONTAINSSTRING(Sentence, [Word])
    )
    RETURN
    CONCATENATEX(FoundWords, [Word], ", ")
     
    Second column:
    CountColumn =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER('Table', 'Table'[NewColumn] = EARLIER('Table'[NewColumn]))
    )
     
    Here is below output for the same
     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
    Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

    Regards

5 Replies

    • rkee10's avatar
      rkee10
      Frequent Visitor
      2 years ago
      Yup, based on given keywords.
  • rkee10's avatar
    rkee10
    Frequent Visitor
    2 years ago

    Yes, the output should give a category and its count. 

    • qqqqqwwwweeerrr's avatar
      qqqqqwwwweeerrr
      Solution Sage
      2 years ago

      Hi rkee10 

       

      If my understanding is correct then here are the step, to achieve what you have you need to create two custom column

      First column

      NewColumn =
      VAR Sentence = 'Table'[Sentence]
      VAR WordList =
      SELECTCOLUMNS(
      UNION(
      ROW("Word", "guitar"),
      ROW("Word", "cook")
      -- Add more words here as needed
      ),
      "Word", [Word]
      )
      VAR FoundWords =
      FILTER(
      WordList,
      CONTAINSSTRING(Sentence, [Word])
      )
      RETURN
      CONCATENATEX(FoundWords, [Word], ", ")
       
      Second column:
      CountColumn =
      CALCULATE(
          COUNTROWS('Table'),
          FILTER('Table', 'Table'[NewColumn] = EARLIER('Table'[NewColumn]))
      )
       
      Here is below output for the same
       

      Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
      Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

      Regards

      • rkee10's avatar
        rkee10
        Frequent Visitor
        2 years ago

        Thanks for this one, qqqqqwwwweeerrr !  By the way, how can I put this count to guitar:

        - How can I play an instrument (or 6 string instrument)?


        I would like to count this under guitar.  So for the answer you provided, the guitar should be 4 if we include this new sentence.  Basically, I want to count words in one category who have the same meaning (synonym).  Let me know if this is clear.  Appreciate your effort on this one.