Can I use several active link to same table?

[slgal]

Hello All,

I have a trouble with building report using Power BI Desktop. Here is my db scheme:

Persons

PK_PersonId | FullName

Orders

PK_OrderId | FK_CustomerId | FK_CashierId

Where FK_CustomerId & FK_CashierId linked with PK_PersonId.

I need build report where I can see:

OrderId, Customer.FullName, Cashier.FullName. Where Customer & Cashier are alias of Person table.

When I try to create few active links it's throw an  error that active link already exists. Right now I see one way to create new view inside db and use it, but I don't like that method. Any body have another idea how can I fix it?

Thanks in advance!

[slgal]

Here is my scheme:

 

The last picture shows the my issue and how I fix it. But I not sure is it a good way.

Looks like @Anonymous propouse the same way. 

But what do you mean by "if you query that will provide the output what you are looking for" ? 

db sctipt just in case

CREATE TABLE [dbo].[Order](
	[OrderId] [int] IDENTITY(1,1) NOT NULL,
	[CustomerId] [int] NOT NULL,
	[CashierId] [int] NOT NULL,
	[Total] [money] NOT NULL,
	[Taxes] [money] NOT NULL,
	[Discount] [money] NOT NULL,
	[Tips] [money] NOT NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
	[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Person](
	[PersonId] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](255) NOT NULL,
	[LastName] [nvarchar](255) NOT NULL,
	[FullName]  AS (([FirstName]+' ')+[LastName]),
	[IsCustomer] [bit] NOT NULL,
	[IsCashier] [bit] NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
	[PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Person] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Person] ([PersonId])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Person]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Person1] FOREIGN KEY([CashierId])
REFERENCES [dbo].[Person] ([PersonId])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Person1]
GO

[v-qiuyu-msft]

Hi @slgal,

In your scenario, it seems that two tables Persons and Orders contain a common filed. If that is a case, you can get all those fields from two tables use SQL joins if you are using SQL database as data source.

If the way doesn’t meet your requirement, please share those tables relationship and contained sample data. Also please provide a screenshot about your expected data. 

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

@slgal

i think Person is like a Role Playing Dimension here

i think Just Creating the Relationship is fine and One can be active other can be just a relation

so, if you query that will provide the output what you are looking for

Or

Create a New table (Replicate Person) as below
Customer = 'Persons' and rename columns and create relationship 🙂

thanks 

[Sean]

@slgal Can you post a picture of the Relationship View? Showing the tables and column names involved?