power bi summarize duplicate error

[obriaincian]

Hi Community,

I am facing an issue when using the summarise function.

What I am trying to do is create a new table with distinct values. To do this I am using summarise.

The query I'm using is:

</Table = SUMMARIZE('Data','Data'[Data.ID],'Data'[Data.Company],'Data'[Data.Department])>

The issue I'm having is that when I add deparement to the quiery I get the following error:

Column '' in Table '' contains a duplicate value and this is not allowed for columns on the one side of a many-to-one relationship

The table I'm using will be used when creating slicers for my dashboard, and is linked to 2 other tables (hence the relationship error).

Just wondering if there is a solution for this. Can't find any concrete answers online

[PaulDBrown]

As you say, it is highly recommended to keep relationships as one-to-many. If the tables you need as slicers cannot work with one-to-many relationships, I suggest you create separate tables for each field. You can use a measure to filter the other silcers  to limit selection options to actual rows of data.

Here is an example based on this simple fact table:

 I've created separate tables for Company and Department using the DISTCINCT function:

The model is as follows:

Create this measure to use as a filter for each slicer, by adding it to the filters in the filter pane:

Filter Slicers = 
COUNTROWS(RELATEDTABLE(FTable))

To get:

I've attached the sample PBIX file

[AntonioM]

Does it work without department added? One thing you could do is remove the relationships first before you add in department and then relink the tables.