Can´t create a relationship between two columns because one of the columns must have unique value

[shawnloj]

Hello, 

I have seen only one other post on this, but my issue seems to be a bit different. 

I am getting this error when I try to create a relationship between tables per an ID. 

I created an ID column for each table. The ID's match text from another column, so there may be the same ID number to represent departments with open positions. I assume this may be the issue. But I am not sure how to get around it? 

I have also tried use another cardinality but it wont let me. it says it wont accept them. 

Thank you

[v-venuppu · Accepted solution]

Hi @shawnloj ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @speedramps for the prompt response.

You need a single dimension table with unique department names or codes that serves as the lookup (1 side) in relationships to the fact tables.

I have replicated the scenario using sample data as per your requirement.Please go through the attached PBIX file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[speedramps]

I want to help you but your description is too vague. Please write it again.

Can Departments have more than one Position?  Probably yes. 
Can Position can have more than one Departments ?  Probably no. 

In which case you need .....

Please don't just copy and paste your data into the forum without renaming tables and field to a user friendly name. We dont what "dir or ind" and "ID" is !

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[shawnloj]

Thank you for the information. 

I appreciate the update. Here is a more detailed update on my question: 

I have 3 table pulling from 3 difference places. 

- Dir or Ind - excel spreadsheet that shows department names and budgeted number for each department. it also then has a group column that is tied to the departments. 

- Secont table is called HR Table - this pulls from a SQL database and has department, employee name, active status and title 

-third is called Recruiting - this has position title, department and status. 

I added a ID number to each table that ties to the department name for each table, The ID is to allow for each table to have a tied to relationship so I can add a Visual that shows the information below:

When I try to tie the ID's I get the error as mentioned in the original post. The department will only show one time in the dir or ind but it could show multiple times in the other two tables because multiple active employees will be in the same department and also the open positions may also have more than one within one department. 

I am trying to better automate the process of updating this information by having the dept_description be from the HR table from SQL, the budget amount come from the dir or ind, the active staff come from the HR table and the count of positon name be from the recruiting table to show total open positions. each column needs to match so its better automated. 

But it gives me that error when I try to creat a relationship with each Table using the ID's. The ID is created using the IF function (ex. if(department name = "IT", 1))

thank you