Forum Discussion

TD21's avatar
TD21
Icon for Helper II rankHelper II
6 years ago
Solved

SELECTCOLUMNS OR SUMMARIZE that drops duplicate records

I have a table with over 100 columns, and one of which is called REQUEST_ID

 

I would like to use either SELECTCOLUMNS or SUMMARIZE to create a new table that selects COLUMN_A, COLUMN_B, COLUMN_C, and REQUEST_ID. But, I would like to drop any records where the REQUEST_ID is not unique in the table (i.e. count >1).

 

Could someone kindly assist me with the DAX formula? Thanks.

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi TD21 , 

    You could refer to below expressions to see whether it work or not(not sure which result you want).

    columnAcolumnBID

    a dd 1
    a ee 1
    a ff 2
    a ff 3
    b tt 1
    b tt 2
    b tt 2
    b yy 4
    b yy 5

     

    Table 2 = CALCULATETABLE(SUMMARIZE('Table','Table'[columnA],'Table'[columnB],'Table'[ID]), FILTER('Table',CALCULATE(COUNT('Table'[ID]))<2))

     

    or

    Table 3 = CALCULATETABLE(SUMMARIZE('Table','Table'[ID],'Table'[columnA],'Table'[columnB]), FILTER('Table',CALCULATE(COUNT('Table'[ID]),ALLEXCEPT('Table','Table'[ID]))<2))

    Best Regards,
    Zoe Zhi

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    TD21 , better do it on power Query.

    1. Duplicate table, delete other columns, and delete duplicate rows.

    2. Select the columns, Add as new query, remove duplicates

     

    Same option like this

    https://www.myexcelonline.com/blog/remove-duplicates-using-power-query/

     

    In case of dax

    summarize(Table,Table[REQUEST_ID],Table[COLUMN_A], Table[COLUMN_B], Table[COLUMN_C])

     

    Will give a unique combinations. If any column/s is causing duplicate do like

    summarize(Table,Table[REQUEST_ID],Table[COLUMN_A], Table[COLUMN_B], "COLUMN_C" ,max(Table[COLUMN_C]))

     

    You can do for all column other than REQUEST_ID

    • TD21's avatar
      TD21
      Icon for Helper II rankHelper II
      6 years ago

      amitchandakI forgot to mention that we are using Connect Live to a SSAS tabular cube, so no Power Query I guess.

       

      I don't quite understand what you meant by "You can do for all column other than REQUEST_ID", but let me give the DAX a try. Thank you for responding and helping out.

      • dax's avatar
        dax
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi TD21 , 

        You could refer to below expressions to see whether it work or not(not sure which result you want).

        columnAcolumnBID

        a dd 1
        a ee 1
        a ff 2
        a ff 3
        b tt 1
        b tt 2
        b tt 2
        b yy 4
        b yy 5

         

        Table 2 = CALCULATETABLE(SUMMARIZE('Table','Table'[columnA],'Table'[columnB],'Table'[ID]), FILTER('Table',CALCULATE(COUNT('Table'[ID]))<2))

         

        or

        Table 3 = CALCULATETABLE(SUMMARIZE('Table','Table'[ID],'Table'[columnA],'Table'[columnB]), FILTER('Table',CALCULATE(COUNT('Table'[ID]),ALLEXCEPT('Table','Table'[ID]))<2))

        Best Regards,
        Zoe Zhi

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