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 tr...
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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi hidenseek9,

You uploaded two same files, please also upload another different file.

Thanks,
Lydia Zhang

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

  • hidenseek9's avatar
    hidenseek9
    Post Patron
    9 years ago

    Anonymous

     

    Thank you for your reply.

    I deleted the duplicate (20191666) from the master file.

    However, the relationship still cannot be created...unfortunately.

    Do you have any idea why that may be?

     

    A good news is that when I created a new table just as you suggested,

    I was able to create a relationship.

    I have a tough time understanding the DAX equation on the new table you suggested.

    I am pretty sure the table you suggested is exactly what I need, though I need to check.

     

    Many thanks,

     

    H

    Thank you so much for your help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

  • hidenseek9's avatar
    hidenseek9
    Post Patron
    9 years ago

    Anonymous

     

    Yes, I used the "Remove Duplicates" option, but the relationship

    could not be created.

     

    However, I created a new table, using a combination of

    Filter and Distict DAX and I was able to create a relationship.

    Thank you for the advice.

    I understand now how to use the Filter and Distinct DAX now.

     

    Appreciate your support!

     

    H