Forum Discussion

webportal's avatar
webportal
Impactful Individual
7 years ago
Solved

Remove duplicates in summarize table

I want to remove duplicate rows of a union of two tables using DAX.

 

This is my code:

Global = 
VAR Table1= SUMMARIZE(GLAccounts;GLAccounts[AccountID];GLAccounts[AccountDescription];GLAccounts[Name];GLAccounts[GroupingCode];GLAccounts[ChaveConta])
Var Table2= SUMMARIZE(GLEntries;GLEntries[AccountID];GLEntries[AccountDescription];GLEntries[Name];GLEntries[GroupingCode];GLEntries[ChaveConta])
RETURN
UNION(Table1;Table2)

 

I know if I create another summarize table over this one, I'll remove the duplicates, but isn't there a way to do the whole thing in a single table?

 

Thanks for helping!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    DISTINCT () should work for you. Although be aware that as with DAX in general it will be case insensitive.

     

    example

     

    EVALUATE
VAR _Table =
    DATATABLE (
        "Column1", STRING,
        "Column2", STRING,
        "Column3", INTEGER,
        {
            { "A", "a", 1 },
            { "A", "A", 1 },
            { "A", "B", 2 },
            { "A", "B", 2 }
        }
    )
RETURN
    DISTINCT ( _Table )

    returns

    Column 1, Column2, Column3
"A", "a", 1
"B", "B", 2

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    DISTINCT () should work for you. Although be aware that as with DAX in general it will be case insensitive.

     

    example

     

    EVALUATE
VAR _Table =
    DATATABLE (
        "Column1", STRING,
        "Column2", STRING,
        "Column3", INTEGER,
        {
            { "A", "a", 1 },
            { "A", "A", 1 },
            { "A", "B", 2 },
            { "A", "B", 2 }
        }
    )
RETURN
    DISTINCT ( _Table )

    returns

    Column 1, Column2, Column3
"A", "a", 1
"B", "B", 2
    • JoachimSA's avatar
      JoachimSA
      Helper II
      4 years ago

      can you do the distinct also only based on 1 column ? I struggle with this because I have a column that I want to use as key for a 1:m relationship...the table variable is built using summarizecolumns

      Thanks