Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jorismo
Helper II
Helper II

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

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

View solution in original post

10 REPLIES 10
MattAllington
Community Champion
Community Champion

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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!

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.

 

 

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

OK found a solution.

 

Created an new datasource with only the names and that worked. 

 

Thansk all!

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

 

 

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.