cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Creating Relationship Question

This seems to be super simple but for some reason I am getting an error message that says I can't create a relationship between these two columns becasue one of the columns mush have a unique values.  I am trying to use PEN ID as the join criteria.  Any help would be appreciated.

Here is what I have in my two tables.

Pen Table:

 Pen ID PEN 5001 1 5002 2 5003 3 5004 4 5005 5 5006 6 5007 7 5008 8 5009 9 5010 10 5011 12 5012 14 5013 16 5014 18 5015 20 5016 21 5017 23 5018 24 5019 99

FFAPOC Table:  There are other columns but they are not relevant to the relationship.

 DATE Pen ID PEN 09/26/2016 5016 21 09/26/2016 5017 23 09/27/2016 5016 21 09/27/2016 5017 23 09/28/2016 5016 21 09/28/2016 5017 23 09/29/2016 5019 99 09/29/2016 5016 21 09/29/2016 5017 23 09/30/2016 5019 99

1 ACCEPTED SOLUTION
Super User

Great, will you accept this as a solution?

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

3 REPLIES 3
Super User

one of the tables in the relationship must have unique  values, i.e FFAPOC should be the many side and the pen should be on the one side i am assuming?  you therefore need to clear out the duplicates of pen.

the best way to check if you have duplicates is to create 2 measures on the table

no of rows = countrows(pen)

count unique values  = distinctcount('pen'[pen])

if those 2 numbers are the same then you good if you have more rows than unique values you have duplication going on

stick them on a matrix and sort by nowofrows to find offending pen

also if you have any blank fields in pen that will also create an issue, pad those values with a -1  (something that has no business value)

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Regular Visitor

@vanessafvg Thank you so much.  Looks like PowerBi was seeing the blanks as nulls and counting those.  This little trick was awesome and was able to create the relationships.

Super User

Great, will you accept this as a solution?

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors