cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jorismo Regular Visitor
Regular Visitor

Trouble Creating Relationships

 

HI all,

 

I've tried the solutions that I've found in previous tickets but it still won't work for me.

Everytime I want to create a relationship between the different sql tables I get the error:

You can't create a relationship between these two columns because one of the columns must have unique values

 

Ok what I want to do:

 

I've got 3 data sources, those are 3 different sql sources.

But they all got 2 items in common: the name of the analyst and the group.

So I want to create the relation ship for the name within those 3 tables, and for the groups.

Name is mentioned in red, group is mentioned in blue.

 

https://photos.app.goo.gl/mRk0HEJodQcKFlZL2

 

The name&group table has the analyst_name which contains the same names (and format of names) as Assignee (Tickets) and assignee_full_name (Changes).

 

The goup_name (Name&group) has the same names of the group (and format) as Group_name(Tickets) and group_name(Changes).

 

What I want to create:

- an visual report with some data from the tickets and changes table.

- possible to filter on the group-name and/or analyst name

 

I hope someone can give me an helpfull hand

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Trouble Creating Relationships

@jorismo,

 

You may add a new table and build relationship on the concatenated column.

Table =
ADDCOLUMNS (
    DISTINCT ( 'Name&group' ),
    "Name | Group", 'Name&group'[Name] & " | "
        & 'Name&group'[Group]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Super User
Super User

Re: Trouble Creating Relationships

Have a read of my article here https://exceleratorbi.com.au/relationships-power-bi-power-pivot/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
jorismo Regular Visitor
Regular Visitor

Re: Trouble Creating Relationships

Hey,,

I’ve read your article about creating relationships, tried again with the things I’vee read but still I’m ,ot able to create a good relationship.

 

regards

Super User
Super User

Re: Trouble Creating Relationships

Do you have a single column in the name and group table that has unique values?  It doesn't look like it.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
jorismo Regular Visitor
Regular Visitor

Re: Trouble Creating Relationships

Hi,

 

I've got a colum in the Changes table named 'Assignee_full_name' wich contains the names from engineers. The same names are also in the table Tickets in colum 'Assignee'. 

And in the table Name&Group the 'Analyst_name' colum has also those values.

 

What I want to do:

- I'm creating an dashboard with the number of tickets and changes on it, via a slicer I can select an engineer and it must provide me the number of tickets and changes that only this engineer has handled.

 

so the slicer will have the field: Name&Group>analyst_name.

 

But before it can show me the data it must have a link towards the other tables.

 

Many thanks for your help!

jorismo Regular Visitor
Regular Visitor

Re: Trouble Creating Relationships

Ok to make it more visible I've created some example data.

(I can't share the original files due to company information)

 

Here you can find the example data

Data

 

 

I've also made a dashboard like I want to have.

 

In short:

- I want to get an general overview about how many bikes and mobilehomes are selled

- It must be filtered with date (so I want to view it in a timespan)

- It can be filtered on name or group

 

 

I hope someone can give me a helping hand.

 

I know this data came from an xls file, in my original request it's from an sql reporting database.

 

 

mow700 Regular Visitor
Regular Visitor

Re: Trouble Creating Relationships

I'd add a concatenated column as the key to all 3 of your tables, and remove the duplicates from your Name&group table.  

Here is an example:

https://1drv.ms/u/s!AoqLdf_zgUezgawgy7a2jSOs7Syi4w 

 

snip_20171030121506.png

 

 

jorismo Regular Visitor
Regular Visitor

Re: Trouble Creating Relationships

I can't access the file that you've created to have a look at it, sorry!

jorismo Regular Visitor
Regular Visitor

Re: Trouble Creating Relationships

if you add an concatenated colum how did you do it?

And removing the duplicate names in my names&groups table, wouldn't dit result in an loss of data?

 

Some names are indeed 3 or more times in the name&group table but it's because they also appear in different groups.

 

Pfff really stuck on this

Community Support Team
Community Support Team

Re: Trouble Creating Relationships

@jorismo,

 

You may add a new table and build relationship on the concatenated column.

Table =
ADDCOLUMNS (
    DISTINCT ( 'Name&group' ),
    "Name | Group", 'Name&group'[Name] & " | "
        & 'Name&group'[Group]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.