Cannot create active relationship between two tables

[Fourthsky]

I have a very simple relational structure that I am attempting to build for my data model. 

I am using a Direct Query data source that has many tables but no built in relational model.

This question relates to 3 tables only.

Table 1: Employee ID Index Table: Created by myself to link together other tables. EmpID is just a whole number list from 1 - 5000.

Table 2: TicketLabourEntries: Column "EmployeeID" is a whole number column and could have duplicates.

Table 3: Users: Column Userid is a whole number list and contains no duplicates.

I am trying to create a 1:1 relationship from Employee ID Index Table:EmpID -> Users:UserId.

The reason is so that I can attach user names to the employee ID's found in TicketLabourEntries table in a visual. TicketLabourEntries has no employee names in the table. Names are in the Users table.

I get the following errors:

and this one when I make the direction Both.

Any insight as to what I am doing wrong here would be appreciated.

[KingFedUp]

Not many insights on this topic; people are clearly guessing. Why are relationships not more intuitive. Why doesn't Power BI list the problem 'active set of indirect relationships'?

[bcdobbs]

I think it's because of the cardinality. Screen shots show 1:1 but your text suggest there may be duplicate ids in the user table.

Manually specifiy it as 1:Many with your user table on the many side. 

[Fourthsky]

There are no duplicates in the UserId so I think it should be 1:1. Even so I tried 1:* and I get this with both single and both as cross filter directions.

[RicoZhou2]

Hi @Fourthsky ,

In addition to bcdobbs's reply, here I have a test by direct query for Power BI Dataset or AS.

In my sample "Emp ID" and "User" tables are enter tables, and my data model is in Mixed mode.

Here I can create a relationship between "Emp ID" and "User". Please check whether there is a relationship between "TicketLabourEntries" and "User" table in Manage Relationship.

Or you can try to create inactive relationships and then create measures by USERELATIONSHIPS().

If this reply still couldn't help you solve your issue, please share more details about your storage mode, data source and so on.

Best Regards.

[bcdobbs · Accepted solution]

A few questions then:

1) What is the data source you're using direct query on? If this is direct query against SSAS or a power bi dataset then what you're trying to do won't work. (Can try and explain more if that's the case).

2) If the direct query source is plain sql relationsal database then I don't understand the need for the index table? Can you explain?

3) How have you created the index table? Eg is it a calculated table or in power query.