Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
leo3690
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 IDPEN
50011
50022
50033
50044
50055
50066
50077
50088
50099
501010
501112
501214
501316
501418
501520
501621
501723
501824
501999

 

 

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

DATEPen IDPEN
09/26/2016501621
09/26/2016501723
09/27/2016501621
09/27/2016501723
09/28/2016501621
09/28/2016501723
09/29/2016501999
09/29/2016501621
09/29/2016501723
09/30/2016501999

 

 

relationship.jpg

 

 

 

 

1 ACCEPTED SOLUTION

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!




View solution in original post

3 REPLIES 3
vanessafvg
Super User
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!




@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. 

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!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.