Table Import error after applying Manage Aggregations

[itsmebvk]

I am creating a basic model connecting to Synapse DB using Direct Query Mode. These are the steps I followed.

1) Using Direct Query loaded Fact_Table,Dim1, Dim2,Dim3 tables.

2) I created Aggregation Table using following SQL

SELECT [Dim1]

      ,[Dim2]

         ,[Dim3]

      ,sum([Measure]) Measure

  FROM [Fact_Table]

  Group by [Dim1]

      ,[Dim2]

         ,[Dim3]

3) Created relationships between tables as highlighted in the screenshot

 

 

4) After that applied Manage Aggregations on “Aggregation Table” (Sum on Measure and Group by on Dim1,Dim2,Dim3)

5) Once I am done with Manage aggregation when change the “Storage Mode” to Import I am getting following error

Column 'Item_number' in Table ‘Dim1’ contains a duplicate value '00000000000001122'

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.“

By looking at the error I can sense that this is something to do with Duplicate values in Dim1 table,  but just for one report purpose I cannot really change the entire Dim1 table.

Please suggest any work arounds which can help to fix this issue?

[V-pazhen-msft · Accepted solution]

@itsmebvk 

As the workaround, try create a distinct table as a bring to connect Dim1 and Aggregation Table.

Distinct item_number = Distinct(Dim1[Item_number])

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.