Getting "not allowed for columns on the one side of a many-to-one relationship" errors

[Anonymous]

I have a PBIX file with various tables and relationships, among them initially everything was working fine, but whenever I add some data in the .xlsx file I am getting this error.
"Column 'metric' in Table 'CYTD-percentage' contains a duplicate value 'Application Availability - Platinum' and this is not allowed forcolumns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."

I have another table 'Availability' with exactly the same function and relationship as 'CYTD-Percentage', everything works seamlessly for table 'Availability' but not for the 'CYTD-percentage'.

Any idea what the issue is here?

[jdbuchanan71]

@Anonymous 

In your excel file, select the whole data range except the headers and go to Data > Remove Duplicates.

Leave only the 'metric' column checked and hit OK.

If you see a notice like this you know you have a duplicate in that column.

You can undo the Remove Duplicates step then put use a COUNTIF against the metric column to find the dupe.

[Anonymous]

Hi @jdbuchanan71 ,
The data will have duplicate values. As the data in metric will increase with the increase in date, so I need those values.

The above pic is of the table 'CYTD-percentage'.
And the following is for the table 'Availability'

The main concern is why I'm getting the error in one case and none in another and how to remove those errors.

[jdbuchanan71]

It sounds like you need a Metrics table that will have each metric once and you can link it into your data tables.   You can create something like that using this dax.

Metrics = 
DISTINCT(
    UNION(
        DISTINCT(CYTD-percentage[Metric]),
        DISTINCT(Availability[Availability])
    )
)

[Anonymous · Accepted solution]

@jdbuchanan71 ,
Figured out a solution, the 'Availability' table already has repeated data in the metric columns. Power BI is not treating it as a column with only unique values and allows it to form the relationship between different tables, whereas the table 'CYTD-Percentage' metric column has only unique values, as a result, Power BI expects only unique values in the metric column. And when I add new data in the column it throws error "not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table".


So I deleted the 'CYTD-Percentage' table from Power BI and added dupliacte data in metric column in input file, then imported it in Power BI, formed all necessary relations, and it worked.
Please, let me know your thoughts.

[amitchandak]

@Anonymous , I doubt either one side of the table is getting a duplicate value or a blank value on the join column.