Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Struggling to figure this one out, but im sure there is a simple way im not seeing!
Im trying to link a large data set from a survey of various things.
On one part of the dataset im asking to rate satisfaction of 3 services. The columns are headered up as "Service 1" "Service 2" "Service 3" "Service 4". All these columns have "Very Satisfied" "satisfied" "Dissatisfied" "Very Dissatisfied" depending on what the user inserted.
I created a seperate sheet within same excel dataset, and created a table which I used the Countif function to count how many of each rating was in the columns (see table)
I have taken this into Power Bi and it works great in clustered graph, however I want to use a slicer to filter the results to show how many of these people using each service are from "area 1", "area 2" or "area 3" (column "Area" in main dataset). There is also an ID column which has unique numbers in each row to use as an identification.
I can't get the slicer to work as Power Bi doesnt recognise there is a relationship between the two tables.
Can someone explain to a newbie how to go about this? I cant figure out how to get a relationship to enable the
thank you 🙂
Solved! Go to Solution.
I make a test with your example dataset.
1. in Edit Queries
(1)select "Service1 a-Usefulness " to "Service1 e-Website and online resources" five columns, then select "Unpivot columns" (under "Transform" tab)
(2) split columns
click on column [Attribute], select split columns by delimeter
click on column [Attribute.2], select split columns by delimeter
then rename columns
[Attribute.1]->[service]
[Attribute.2.2]->[service-sub]
close&apply
2. create a measure
Measure = COUNT(Sheet6[Value])
add columns and measure in a matrix visual
3. add columns and measures in the column chart as below, you will see
Best Regards
Maggie
I make a test with your example dataset.
1. in Edit Queries
(1)select "Service1 a-Usefulness " to "Service1 e-Website and online resources" five columns, then select "Unpivot columns" (under "Transform" tab)
(2) split columns
click on column [Attribute], select split columns by delimeter
click on column [Attribute.2], select split columns by delimeter
then rename columns
[Attribute.1]->[service]
[Attribute.2.2]->[service-sub]
close&apply
2. create a measure
Measure = COUNT(Sheet6[Value])
add columns and measure in a matrix visual
3. add columns and measures in the column chart as below, you will see
Best Regards
Maggie
Thank you VERY much v-juanli-msft 🙂
This is amazing and would never be able to figure that out by myself.
Hi,
I cannot visualise your entire table. Show the entire/all Tables and show the expected result.
Large dataset (a section of it)
Countif Table in another tab of excel (Just an example - i know the amounts dont equate to the table above!):
And the result I want in Power Bi is:
The dataset is absolutlely massive as this was a large survey - this section is only one small part of it. If i can filter by area (Campus type on this visual) then i'll be able to pull this into the rest of the service results/graphs (100+ of them).
Any guidance would be greatly appreciated!
Hi,
In the Query Editor, select the first 3 columns and select "Unpivot other columns". Now build your visual.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |