Forum Discussion

hidenseek9's avatar
hidenseek9
Post Patron
9 years ago
Solved

Cannot create relationships

Hi Power BI Community!

 

I need help from you on creating relationships between data.

I am working on multiple excel files in power BI and I want to create relationships with one another.

I am trying to track revenue by client numbers.

 

I have a data in Power BI with a master client data with all client numbers.

I have another file in Power BI with revenue data with client numbers.

 

In Power BI, all client numbers is in text format and when I try to create relationships with the two.

It shows an error and does not create relationships.

I also tried with number format but that did not work as well.

 

I checked the data in excel file to make sure that these numbers are correct.

 

I encountered an issue with relationship management before as well.

 

How can I solve the above issue?

 

Appreciate your support!

 

Many thanks,

 

H

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi hidenseek9,

    When creating relationship using your sample data, I get the following error message, as the message mentions, one of the columns you use to create relationship must have unique values.


    In your scenario, you can remove duplicates(20191666) in your MasterFile as shown in the following screenshot, then create replication between the two tables.


    If you want to reserve duplicates in MasterFile, please create another new table using the following DAX(replace table name with your own), then create relationship between the new table and MasterFile, and create relationship between the new table and actual data file.

    NewTable = 
FILTER(
DISTINCT(
UNION(
DISTINCT(Sheet2[#]),
DISTINCT(Sheet3[Vendor])
)
),
NOT(ISBLANK(Sheet2[#]))
)



    Thanks,
    Lydia Zhang

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi hidenseek9,


    Do you use the "Remove Duplicates" option I mentioned in the second screenshot to remove duplicates?  If so, you should be able to create relationship between original tables. "Remove Duplicates" option is available in Query Editor of Power BI Desktop.

    Thanks,
    Lydia Zhang

16 Replies

    • hidenseek9's avatar
      hidenseek9
      Post Patron
      9 years ago

      Hi Phil_Seamark

       

      Thanks for a quick response.

      The error message says,

       

      cannot create a relationships with these two columns due to unmatched value in either column

       

      I am translating my original language in English, but it says something along above line.

       

      Do you know how to solve this?

       

      Thanks,

       

      H

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi hidenseek9,

    I am trying to test your scenario but I am unable to reproduce the above error message. Could you please share sample data of your tables and post a screenshot about your scenario?

    Thanks,
    Lydia Zhang

    • hidenseek9's avatar
      hidenseek9
      Post Patron
      9 years ago

      Anonymous

       

      Thank you for your reply!

      Basically, I am trying to create a relationship with below two numbers.

      I have checked in excel file that a master data file has all the numbers

      in the actual data, but somehow a relationship cannot be created.

       

      Hope below screenshots help.

       

      Many thanks,

       

      H

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi hidenseek9,

        I create sample data in my excel, I can successfully create relationship after import the data to Power BI Desktop. Could you please share your Excel file so that I can test? You can upload Excel file to OneDrive and post shared link of the Excel file here.

        Thanks,
        Lydia Zhang