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

[don075]

Got three tables with the following structure and data

Main powerbi dashboard must show the data from Trns table in matrix visual with the users (linking with users with fields f1 and f2) group by fields f1,f2,f3,f4

When an item is selected from the matrix visual, it should find the matching data in Crumb table using fields f1,f2,f3,f4. Trns and Crumb only got many to many relationship. Same between Users and Trns

My issue is creating relationships between table.

When trying to create relationship From Users to Trns: Connect Users.f1 to Trns.f1, I get the following.

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

I am not sure how to continue. User table will have the same f1, f2 values duplicated for multiple users

Same was Trns table will have f1,f2,f3,f4 values duplicated. So, cannot link Trans and Crumb either.

Any help is greatly appreciated.

SQL queries to create tables and insert sample data.

CREATE TABLE Users (

uid VARCHAR(50) NOT NULL, 

f1 VARCHAR(50) NOT NULL, 

f2 VARCHAR(50) NOT NULL

)

INSERT INTO Users (uid,f1,f2) VALUES

('u1','11','2000'),

('u1','11','3000'),

('u1','11','4000'),

('u1','11','5000'),

('u1','11','6000'),

('u1','11','2700'),

('u1','11','7000'),

('u1','23','8000'),

('u1','23','5600'),

('u2','33','1000'),

('u2','33','2000'),

('u2','34','3000'),

('u2','56','4000'),

('u3','11','2000'),

('u3','11','2000'),

('u3','15','2000'),

('u3','16','2000')

CREATE TABLE Trns (

syear VARCHAR(50) NOT NULL, smonth VARCHAR(50) NOT NULL, f1 VARCHAR(50) NOT NULL, f2 VARCHAR(50) NOT NULL, f3 VARCHAR(2) NOT NULL,f4 VARCHAR(50) NOT NULL, gtot int

)

INSERT INTO Trns (syear,smonth,f1,f2,f3,f4,gtot) VALUES

('2021','1','11','2000','AA','11111',1500),

('2021','2','11','2000','AA','11111',500),

('2021','3','11','2000','AA','11111',1000),

('2021','3','11','2000','AA','11111',11500),

('2021','4','11','2000','AA','11111',13500),

('2021','4','11','2000','AA','11111',14500),

('2021','4','11','2000','AA','11111',500),

('2021','5','11','2000','AA','45689',14500),

('2021','5','11','2700','AA','11111',14500),

('2021','6','11','3000','AA','11111',14500),

('2021','1','15','2000','AA','11111',14500),

('2021','2','34','3000','AA','11111',14500),

('2021','2','56','3000','AA','11111',14500),

('2021','3','56','4000','AA','11111',500)

select * from Trns

CREATE TABLE Crumb (

syear VARCHAR(50) NOT NULL, smonth VARCHAR(50) NOT NULL, f1 VARCHAR(50) NOT NULL, f2 VARCHAR(50) NOT NULL, f3 VARCHAR(2) NOT NULL,f4 VARCHAR(50) NOT NULL, tot int

)

INSERT INTO Crumb (syear,smonth,f1,f2,f3,f4,tot) VALUES

('2021','1','11','2000','AA','11111',250),

('2021','1','11','2000','AA','11111',250),

('2021','1','11','2000','AA','11111',250),

('2021','1','11','2000','AA','11111',250),

('2021','1','11','2000','AA','11111',500),

('2021','2','11','2000','AA','11111',100),

('2021','2','11','2000','AA','11111',400),

('2021','2','56','3000','AA','11111',10500),

('2021','2','56','3000','AA','11111',4500),

('2021','3','56','4000','AA','11111',100),

('2021','3','56','4000','AA','11111',150),

('2021','3','56','4000','AA','11111',150),

('2021','3','56','4000','AA','11111',50),

('2021','3','56','4000','AA','11111',25),

('2021','3','56','4000','AA','11111',25)

[vojtechsima]

Hi, @don075 ,

well, it seems that none of your columns are unique and from your data, there is not even a clear possible one, because even the user table has duplicates if you take F1 and F2 and combine them to create a surrogate key.

You would have to also add user to this mix creating uid+f1+f2, however, you don't have this UID in your Fact table.
Your user table when creating f1+f2 have issues that u1 and u3 share the same f1+f2.

So, you need to figure out if you can somehow make at least some surrogate key to make data unique in Dimension table and then you can have the same value multiple times in Fact tables.

[don075]

Thanks for the response. If we ignore the Users table for now, could I link Trns and Crumb tables with f1, f2, f3, f4, sYear, sMonth?
Then have the Matrix visual show data from Trns and when the user selects a record, show the matching ones from Crumb?

[Irwan]

hello @don075 ,

not sure if this will help, but is it possible to create custom unique id using CONCATENATE()?

for example: 

in 'uid,f1,f2' table, concatenate all of them to became u1112000 (for the first line).

this might be usable as unique id.

Thank you.

[don075]

Thanks for the response.
I dropped the Users table for now. Trying to link only the Trns and Crumble tables.
In my SQL server, I modified both Trns and Crumble tables and added sKey column. This contains concatenated fields of f1+f2+f3+f4+syear+smonth

Created a BridgeTable witk sKey that contains Distinct f1+f2+f3+f4+syear+smonth
Linked BridgeTable and Trns with sKey
Linked BridgeTable and Crumb with sKey

Added a slicer and added sMonth from Trns.
Added Matrix visual and added fields from Trns
Added Table visual and added fields from Crumb.
When I select month from slicer, it refreshes only the Matrix visual. Table visual remains.

Modified the slicer, removed sMonth and added sMonth from BridgeTable.
Now the slicer shows the month but it added a blank entry at the top. However, the BridgeTable doesn't have any blank values.

When the Blank value is selected, it refreshes both Matrix and Table visual with some data. Cannot figure out why only some records are displayed.
Any other month is selected, both visual become empty.
I am not sure what's wrong. Apologies for the questions as I am new to PowerBI. 

Including a screen shot with the dashboard.

When selected blank

When selected Month 1

[ajohnso2]

If you drop your user table and leave your 2 fact tables remaining why not join those 2 together in your database (or SQL, or in power query) to save all this messing about. But as someone has already stated, you do not have any unique keys to join users and fact. Is there a reason you cannot have a user key in your fact tables?

FYI - from info above the reason your slicer is not the behaviour you want is because you have used sMonth from 1 fact table (the other fact table has no relation with the other), your slicer should be using a value from your bridge table if you want it to filter both fact tables.

[don075]

Thanks for the reply. I cannot link this in my database as there are duplicates on both tables. I thought maybe powerbi will let me view crumb data when clicked on Trns table, even with duplicates. 

I have been developing reports with Crystal reports for years and doing this there is so simple, even with duplicates. But the users want this done in powerbi.

[vojtechsima]

@don075 hello,

so probably what you can do, create 4 dimensions f1->f4 with unique values, then connect them respectively with your fact table.

Then create calendar (table with dates worth of at least one year without gaps), create helping columns such as year or month and then connect the year and month you have in your fact table to the Calendar.

Then create slicers from the dimensions and you can operate everything through dimensions.

[Irwan]

hello @don075 

i am not sure but if there is no measure perhaps that is a relationship problem since it looks like to be connected through blank value.

i believe the smonth in slicer should be taken from table with relationship with data in table visual.

if possible, please share your sample pbix with removing any confidential information.

Thank you.

[don075]

I have attached the report. recreated the report with ImportData. My original report was created with DirectQuery. This thread won't let me upload the file. I am including the google share link to the file.

https://drive.google.com/file/d/163e3hhTqZaL-7Z-BfbRFC8N_hPNxdSJ5/view?usp=sharing

[vojtechsima · Accepted solution]

Hello, @don075 ,

the issuse is that you don't have any match, because you have different formatting:
Trns:

BridgeTable

Crumb

if I create temporary one, it works just fine:

[don075]

Thanks a lot. Silly mistake by me.  I had to make one more change to the relationship.

In  the link between BridgeTable and Trns, had to select "Both" for "Cross filter direction"