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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.