Forum Discussion

sarveshpandey23's avatar
sarveshpandey23
Frequent Visitor
6 years ago
Solved

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 u...
Interesting Links
Need Help
Tips and Tricks
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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.

     

v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

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.