Forum Discussion
Unable to create 1:m relationship.
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.
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.
3 Replies
- v-diye-msft
Community Support
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!
- v-diye-msft
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.
