Scorecard Refresh Failure - Duplicate 'Id' in 'Values' table

[TR_KY_PBI]

Working with a client to build a ScorecardScorecard for their leadership team.  It utilizes both linked an unlinked goals.  

I am going to build a Power BI report with a live connection to the Scorecard Dataset to supplement the Scorecard with a few visuals that can be displayed in the client's office, etc.

I have begin receiving the following error when I attempt to refresh the dataset:

Column '<pii>Id</pii>' in Table '<pii>Values</pii>' contains a duplicate value '<pii></pii>' and this is 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

It looks like the Id field in the Values table is a concatination of the Goal Id and the date of the value update, which seems like it is asking for trouble, because there is nothing stopping a user from making two updates on the same day.

I went through all the goals and subgoals to remove any concurrent checkings (i.e., ensured that there was only a single checkin per day for each goal), but the dataset is still failing for the same reason (duplicate Id in the Values table).

How can this be corrected?  This is becoming a critical issue for the client, and needs to be resolved ASAP.

[SimpsonDM]

@TR_KY_PBI did you manage to resolve this issue?

I am hitting the same error.

I am creating goals via the PBI api, when the dataset is then refreshed the following error is presented:

Data source errorColumn '<pii>Id</pii>' in Table '<pii>Values</pii>' contains a duplicate value '<pii></pii>' and this is 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. Table: Values.

After adding my first goal I was able to refresh the dataset and see that the values table had a record with a blank value in the Id column. I assume on adding my second goal via the API, this also generates a record in the values table with a blank Id - which is then erroring due to duplicates when the dataset is refreshed.

It would be great to hear if you had any success with this.

Thanks

[TR_KY_PBI]

Hi @SimpsonDM -

Unfortunately, I ended up having to re-make the entire scorecard.  We were still early in the deployment, so we didn't loose anything other than a few hrs of work.  Very frustrating.  However, we have been running for several weeks now w/o issue.  We were creating goals manually, rather than via API.

[SimpsonDM]

I've at least figured out what the issue was

When you create a goal without an initial value, the service seems to add a record to the values table with a blank id. If you add a second goal, also without a value, then it throws the error above (duplicate value '<pii></pii>')

To get around this, once I created the goals I added an initial check in for some time in the past that would be outside of my current date range (01/01/2000). Once this check in was added for all goals, I was able to refresh the dataset without issue.

[TR_KY_PBI]

Any other recommendations?  

I get that Goals / Scorecards is a preview feature, but this is a pretty critical miss and will put my client in a tough spot.  

[Anonymous]

Hi @TR_KY_PBI ,

Could you please download the pbix from the Power BI Service. You can find the dataset in the workspace.

More details: Create scorecards and manual goals in Power BI 

You can open the pbix file on the Power BI Desktop and refresh it to find the error. And you can download and operate it without affecting the user's use.

Similar post: Get "not allowed for columns on the one side of a many-to-one relationship" errors 

•  Manage Relationships
• enable the relationship

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TR_KY_PBI]

Unfortunately, I am unable to download the pbix file from any scorecard dataset.  

I tested this with the dataset that was producing the error and with a few other test Scorecard datasets as well, and received the same result.  It appears that Microsoft does not allow downloads of the pbix for Scorecard datasets?

The only way I am able to connect to this dataset from a pbix is through a live connection

While this shows the tables/relationships, it does not allow me to refresh each table independently to help me identify the error.

Is there another way to find the source data for the scorecard?  Is it sitting in Azure somewhere that I can locate/analyze it to find and get rid of the duplicate?

[TR_KY_PBI]

Still not able to find a solution to this issue.

Is this a known potential issue with Scorecards?  

[Anonymous]

Hi @TR_KY_PBI ,

You can seamlessly integrate existing scorecards into reports, and even make updates to a scorecard directly from Power BI Desktop.

Prerequisites:

• You must be signed in to Power BI Desktop and have a Power BI Pro license.

More details: Create a scorecard visual in Power BI (preview) 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TR_KY_PBI]

My client has already invested many hours in building their scorecard.  I am not trying to display the scorecard directly in Power BI through the new preview visual, I am trying to create a connection to the scorecard dataset through Power BI Desktop.  

To be clear on the issue:

The Scorecard Dataset refresh fails due to a duplicate value in the ID column on the Values table that is automatically generated in Power BI service from the scorecard.  How can this be corrected without having to completely recreate the scorecard?

[Anonymous]

Hi @TR_KY_PBI ,

If you are using this function in the link below？

Create scorecards and manual goals in Power BI (preview)

If not, is that the dataset called scorecard is connected in Desktop and an error is reported during the data conversion?Could you please provide a screenshot with the issue.

“Column '<pii>Id</pii>' in Table '<pii>Values</pii>' contains a duplicate value '<pii></pii>' ”. The pii column now have duplicate value. Please check that does the Id column of the table Values in the data source actually contain duplicate values?

If your report is publish from Power BI Desktop, I'd like to suggest you add custom steps to filter the duplicate records at Power Query side.

If you directly get data from service side, I think it is impossible to modified at Power BI Service, you need to deal with these duplicate records at interface side.

I have also found a similar post, please refer to it to see if it helps you.

Dataset refresh failed due to primary key violation 

• Solution 1 (Recommanded) - Remove duplicates from Email column in Power Query window
• Solution 2 - Update the relationship to Many to Many

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SathyaV]

Hello All,

Did anyone had luck on the issue?
I am facing same issue and using out of box scorecard functionality in Power BI. when trying to connect to scorecard in Power BI desktop as dataset via "live connection" the below error pops-up.

Column '<pii>Id</pii>' in Table '<pii>Values</pii>' contains a duplicate value '<pii></pii>' and this is 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. Table: Values.

Its very strange that it is working few weeks before and all of sudden the issue popsup.

Thank you for any insights. Highly appreciate your time and support!

[TR_KY_PBI]

The dataset was not published from Power BI Desktop.  

It was automatically created in Service when the Scorecard was created following the process described in the link posted by @Anonymous.

The error I receive is as follows:

Again, this is not a dataset that I created.  It was generated automatically from the Scorecard and the data that was entered there.

Unfortunately, I do not know how to check the source data on the scorecard to identify where the duplicate exists.  I know how to establish a live connection from Power BI Desktop to the Scorecard dataset in service, but as this dataset has not been refreshed with the latest data that contains the duplicate, this does not provide any useful information.  If there was a way to connect directly to the Scorecard data other than connecting to the dataset, I'd love to hear about it!