Power BI Relationship?

[aaditya2000]

Hello:

i have 2 tables where i am not able to create one to many or many to one relationship and getting following message "The cardinality you selected is not valid for this relationship".

Default it is creating Many-to-Many and this not helping me.

I am aware of duplicates & uniqueness that matters in creating relationship.

I have tried creating like 3rd table and couple of others suggestion doing google but no luck.

I am looking for any proven workaround to create one to many or many to one relationship.

Any help is greatly appreciated.

Thanking in advance.

[bikram_laishram]

@aaditya2000

I faced a similiar issue. Here's how I fixed it.

I used the table funtion to create a new table

Use the below to create unique ID

Table = DISTINCT(tablename[columnname]) 

Use this table as a junction table to connect both tables. Change the cardinality based on your report requriements

[aaditya2000]

Hello:

the problem is that i have dependency on 2 columns on my main table and one of column as repeated customer id. I cannot create table with distinct values as columns is one to many values in my orginal table.

thanks for your reply.

[aaditya2000]

As you can see from below that because of non-uniqueness values of Table ABC & XYZ, one to many or many to one relationship cannot be created. Many to Many doesn’t helps as it give me cross join. i want to see if there is work around that i can create one to many or many to one. Based on relationship between product_type_no & product_no, i want to display description in my report.

Any help is greatly appreciated.

[bikram_laishram]

Hello,

can you please share an anonymized version of the excel and tell me exactly what you are trying to derive. I can see only three columns, 2 columns in table ABC and 1 column in table XYZ. 

I dont know if it is acceptable, but you can reach me on skype "bikram.laishram". can try to understand what the situation is. 

[aaditya2000]

Hello Bikram:

Thanks for your reply.

i want to form\create one-to-many or many to one relationship from table ABC to XYZ based on column product_type_no & product_no, so that i can retriew "description" column information.

I hope this helps.

thanking in advance.

[v-frfei-msft]

Hi @aaditya2000,

Could you please share your sample data or pbix to me?

Regards,

Frank

[aaditya2000]

i have already shared the sample data, please see above teh screenshot of excel spreadsheet.

To get the product description, i have to form relation from product_type_no to product_no.

Thanks you in advance.

[v-frfei-msft]

Hi @aaditya2000,

We can duplicate the XYZ table in power query and remove Duplicates. Then we can get a new table with unique values and create relationship between tables as you want.

Rgeards,

Frank