Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Many-One Relationship when there are Unique values in both tables

I am attempting to link togther two tables that both have unique values, however, it is not allwoing me to make a one:one relationship- instead making a many:one relationship.

 

I have checked in Excel & Power Bi itself and the table on the many side has all unique values with no blanks 

See above I have ordered count of item#/cmi# in descedning order and the highest count is one and therefore there is all unique values.

 

Thanks in advance for any help,

 

Adam P

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    There are two ways to edit a relationship in Power BI.

    The first method to edit a relationship is using the Editing relationships in the Properties pane in Model view, where you can select any line between two tables to see the relationship options in the Properties pane. Be sure to expand the Properties pane to see the relationship options.

    The other method of editing a relationship is using the Relationship editor dialog, which you can open many ways from within Power BI Desktop.

     

    Please refer to the following document for more information.

    Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello Neeko,

       

      Thanks for your reply.

      I know how to edit teh relatiosnhips but Power bi is not accepting the one:one cardinality taht it shoudl be.

      For some reason, it is detecting dupliacte values in one of the tables which are unique.

       

      Kind Regards, Adam P

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

         

        This is due to the design, and one-to-one relationships are not supported when there are duplicate values in the table.
        You can use the values() function or the distinct() function to create a table with deduplicated values. Then establish a one-to-one relationship with the new table.

        Please refer to my PBIX file.

         

        Best Regards,

        Neeko Tang

        If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.