Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
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] )
Have a read of my article here https://exceleratorbi.com.au/relationships-power-bi-power-pivot/
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.
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
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.
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] )
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
123 | |
109 | |
87 | |
67 | |
66 |