Data shaping and columns

[Anonymous]

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

[v-yuta-msft · Accepted solution]

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

[v-yuta-msft]

@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]

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 · Accepted solution]

@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 · Accepted solution]

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. 

[v-yuta-msft]

@Anonymous ,

Could you please share the sample data so that I could do further analysis?

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy

Please see the link below

https://cclvehicle-my.sharepoint.com/:x:/g/personal/james_bockett_hertzaccidentsupport_com/Eb00gBKFU...

Many thanks

James