Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors