Unable to create 1:m relationship.

[sarveshpandey23]

Power Bi is creating force M:M relationship between Rpac_FinancialDetails_New(Saleperson) and mstbl_UserRoleMapping(varEemployeeId).

mstbl_UserRoleMapping have column "varEmployeeId" which have unique values.

Rpac_FinancialDetails_New have column "Saleperson" which have id of sale person and the values repeats.(Null values are removed)

Power Bi should create 1:m relationship between these to table but it shows an error "The cardinality you have selected isn't valid for this relationship."

Also made a seprate table "Bridge_varEmployeeId" with distinct values of varEmployeeId and SalesPerson together but it still create M:M relationship between them.

[v-diye-msft · Accepted solution]

Hi @sarveshpandey23 

1. Create two measures to double verify whether [varEmployeeId] values are distinct in mstbl_UserRoleMapping table.

TotalRows=COUNTROWS('mstbl_UserRoleMapping')

DistinctRows= DISTINCTCOUNT('mstbl_UserRoleMapping'[varEmployeeId])

After create those two measures, please place them in two card visuals, if results are different, it means there are duplicate [varEmployeeId] values in mstbl_UserRoleMapping table.

2. If there no duplicate values appeared, go to "Edit Queries" and selected the mstbl_UserRoleMapping table where the non-duplicated values resided (the 'one' side of the relationship). right-clicked on the column containing the key values and selected "remove errors". Doing this then allowed me to create the 'many to one' relationship.

[Anonymous]

Hi @sarveshpandey23 ,

I would check if the column has no blank values.

[v-diye-msft]

Hi  @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!