Three columns into one so I can use it as a filter

[twofingertyper]

Hi all, 

I have three columns that contain ratings for performance these are marked 1 - 5 and drawn from an MS form; I have made a measure that tallies these up and presents totals that I can provide in my visual. 

However, what I am not able to do is filter on a specific rating - there are three (resolution, response time and overall user satisfaction) and for each row all three will have a value. If I wanted to plot this on a line graph it would be great to present all the options, and then allow the user to filter out specific values from these three if they wanted to. 

Any ideas as to how best to approach this? I thought about duplicating the rows, but thought that could be excessive - and then thought that it may be an idea to have a table for each area to store the data, but then I'd still need a way to have the single column that provides the option to pick one of the three options. 

Any thoughts very welcome! 

[AnalyticsWizard]

@twofingertyper 

1. Unpivot your data: If your ratings are currently in separate columns for each category, use Power Query to unpivot these columns. This will transform your data so that you have a single "Rating Type" column (containing "resolution", "response time", "overall user satisfaction") and a "Rating" column with the values. To do this, in Power Query Editor, select the columns you want to unpivot, then choose "Unpivot Columns" from the Transform tab.

2. Create your measure: It seems you already have a measure to tally ratings. Ensure it works with the unpivoted data structure.

3. Set up your line graph: Create a line graph with the "Rating Type" as the legend, time or another dimension on the X-axis, and your ratings measure on the Y-axis. This setup will display three lines, one for each rating type.

4. Add a slicer: To allow users to filter specific ratings, add a slicer to your report and link it to the "Rating Type" column. This lets users select which ratings to display on the graph.

This approach avoids redundancy and keeps your model streamlined while providing the flexibility to filter and analyze the ratings data across different dimensions.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[twofingertyper]

@AnalyticsWizard  - excellent, this sounds like an approach I should be able to use. There are other tables linking in, but I will use this approach to perhaps create a different table that just manages ratings (and categories/dates). 

I will update the post when I have had time to trial this soloution, but I was looking at unpivoting the data recently and thought that it sounded like a potential option, so your further guidance means I'll definiately give this a go.

Thank you again  

[HotChilli]

Post some sample data please and show the desired result.

[twofingertyper]

Okay, so I have a table that summarises much of the data - a snippet would be:

Date	resolution	response time	overall user satisfaction
10/10/2023	4	1	3
10/11/2024	5	2	3
11/10/2022	5	5	5
08/07/2023	5	5	5

The values in those three columns are then totalled up and averaged as part of a measures and work fine. 

However, I now want to use a filter so I can have a line graph that has these three column headings featured as lines, as well as having the option to include them in a filter. As the actual values in the columns are just numbers, I can't work them as filters - so what would be the best option here? 

The desired result is a filter that allows the user to choose between the three columns as part of a drop down. 

I may have missed something obvious!

[twofingertyper]

I've now found a couple of ways to get a slicer to look at multiple columns: 

https://community.fabric.microsoft.com/t5/Desktop/Single-Slicer-to-Filter-Multiple-Columns/td-p/3421...

https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/

Which is partly what I want, the other issue would be then having a similar arrangement for a legend but I'm going to start with these and see how things go.