Linking Table

[moragbeedie78]

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 🙂

[v-juanli-msft · Accepted solution]

Hi @moragbeedie78

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

[moragbeedie78]

Thank you VERY much v-juanli-msft 🙂

This is amazing and would never be able to figure that out by myself.

[Ashish_Mathur]

Hi,

I cannot visualise your entire table.  Show the entire/all Tables and show the expected result.

[moragbeedie78]

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!

[Ashish_Mathur]

Hi,

In the Query Editor, select the first 3 columns and select "Unpivot other columns".  Now build your visual.