Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data shaping and columns

Hi

 

I have the below dataset

 

Column O is a question from a survey that asks customers what word(s) best describe the service they received from us. As there are multiple choices to pick from and a respondent can pick multiple words, it splits the question and answers into a number of columns (O - V).

 

I need to use a bar graph to show how many times each word has been selected and be able to filter the visual by date. Does anyone have any suggestions/help/tutorial?

 

Thanks a lot for reading.

James

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    Click query editor-> Transform-> click on the columns then click "Unpivot columns". You will achieve two columns [Attribute] and [Value]. Then create a measure using dax like pattern below:

    Helpful_Count =
CALCULATE (
    COUNT ( Table[Value] ),
    ALLEXCEPT ( Table, Table[Start Date], Table[Attribute] )
)

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    6 years ago

    Thanks v-yuta-msft 

     

    I had to add a little bit in so my measure ended up looking like this:

     

    Helpful_Count = CALCULATE(
    COUNT(Table[Value]),
    ALLEXCEPT(Table,Table[Start Date],Table[Attribute]),
    FILTER(Table,Table[Value]="Helpful")
    )
     
    and then repeated this, changing "Helpful" for the other options that could've been selected. 

6 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Anonymous ,

     

    Change date format to yyyy/mm/dd, then create measures using dax like pattern below:

    Helpful_Count =
CALCULATE (
    COUNT ( Table[Helpful] ),
    FILTER ( ALLEXCEPT ( Table, Table[Start Date] ), Table[Helpful] = "Helpful" )
)

     

    The other measures is similar with the one above, you just need to change "Helpful" with other words.

     

    Finally, drag date column to X-axis and measures to value field in the bar chart.

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-yuta-msft 

      Thanks for the comment.

       

      I have tried the formula but I don't get the result I was hoping for.

       

      I am aiming to have a visual that looks something like the below and to be able to filter it by month (using a slicer)  to show the results for the selected month.

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Many thanks

      James

       

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        6 years ago

        Anonymous ,

         

        Click query editor-> Transform-> click on the columns then click "Unpivot columns". You will achieve two columns [Attribute] and [Value]. Then create a measure using dax like pattern below:

        Helpful_Count =
CALCULATE (
    COUNT ( Table[Value] ),
    ALLEXCEPT ( Table, Table[Start Date], Table[Attribute] )
)

         

        Community Support Team _ Jimmy Tao

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.