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 ...
  • 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

rkee10's avatar
rkee10
Frequent Visitor
2 years ago

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

qqqqqwwwweeerrr's avatar
qqqqqwwwweeerrr
Icon for Solution Sage rankSolution 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.