cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors