Forum Discussion

obriaincian's avatar
obriaincian
Resolver I
4 years ago

power bi summarize duplicate error

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
Need Help

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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's avatar
    AntonioM
    Solution Sage
    4 years ago

    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.