Forum Discussion

IanErler's avatar
IanErler
Regular Visitor
6 years ago
Solved

Google Form Graph

Hello, I started with learning how to use Power BI recently. And I'm having trouble reconstruction a certain graph. I've used google forms for my survey and exported the data to a .xls file. There ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi IanErler,

    It seems like you are trying to expand all text string and get the detail count of each item, right? If this is the case, you can take a look at the following link about the similar requirements:
    Occurrence of value (or) count value(text) in a column 

     

    NEW Table =
VAR _path =
    SUBSTITUTE ( CONCATENATEX ( ALL ( 'Table'[Text] ), [Text], "," ), ",", "|" )
VAR _length =
    PATHLENGTH ( _path )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Text", PATHITEM ( _path, [Value] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _pathtable, [Text] ),
        "Count", COUNTROWS ( FILTER ( _pathtable, [Text] = EARLIER ( [Text] ) ) )
    )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi IanErler,

It seems like you are trying to expand all text string and get the detail count of each item, right? If this is the case, you can take a look at the following link about the similar requirements:
Occurrence of value (or) count value(text) in a column 

 

NEW Table =
VAR _path =
    SUBSTITUTE ( CONCATENATEX ( ALL ( 'Table'[Text] ), [Text], "," ), ",", "|" )
VAR _length =
    PATHLENGTH ( _path )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Text", PATHITEM ( _path, [Value] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _pathtable, [Text] ),
        "Count", COUNTROWS ( FILTER ( _pathtable, [Text] = EARLIER ( [Text] ) ) )
    )

 

Regards,

Xiaoxin Sheng

  • IanErler's avatar
    IanErler
    Regular Visitor
    6 years ago

    Hello Anonymous,

    Thanks a lot for your help. I tried to understand what you did here, but failed to understand the basic.

    But the code worked. I also needed the % of people that answered each option, so I added a new columm dividing the count columm value by the number of people that answered the survey (149), as you can be seen in the pictures below. Do you know if I can show the two values in the graph (the numeric and the %), like the Google one, using the Power BI?

    Table created!

     With a percentage columm now

     

    My Graph

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous 

    Thanks for posting this info.  I used the information provided to create a new table.  However is is not counting each unique occurance of each response, any ideas.  Below are screen shots to elaborate.

     

    Here is a sample of my data

    When I filter to the specific response of "I need more info..."  I see I have 32 rows with this response, some with additional responses.

    But when I use the above Table measure, I get 11 responses for "I need more info...."

    What can I do to get my table to count each unique instance.