Forum Discussion

iamprajot's avatar
iamprajot
Icon for Responsive Resident rankResponsive Resident
8 years ago
Solved

Unique records in SELECTCOLUMNS

I want to get a list of unique records in a new Table which is created using SELECTCOLUMNS.

Currently I am getting all the records which contains duplicate records.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi iamprajot,

     

    1. Extract more than one column.

    Table = ALL('Table1'[Column1], 'Table1'[Column2])

    2. Formula for your scenario.

    Table = summarize('table1', 'table1'[OrderID], "sum", sum('table1'[Price])

    Please give them a try.

     

    Best Regards,

    Dale

8 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    8 years ago

    Hi iamprajot,

     

    If I understand correctly, you are using SELECTCOLUMNS()  to extract values of a column from a table? If so, you can do either:

     

     

    ColumnValues =
VALUES ( 'Table'[Column] )

     

    ColumnValues = ALL('Table'[Column])
    //may contain blank

     

    ColumnValues =
ALLNOBLANKROW ( 'Table'[Column] )
//excludes blank

     

    ColumnValues =
DISTINCT ( 'Table'[Column] )

    These will all return a table containing a column of unique values.

     

    • iamprajot's avatar
      iamprajot
      Icon for Responsive Resident rankResponsive Resident
      8 years ago
      I know all these and it works if we just need 1 column
      BUT the catch is if we need multiple columns, for eg, 1 unique column like OrderID and other columns tat contains aggregates.
      TableName,
      "Column1", Values(OrderID),
      "Column1", SUM(Price)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    have you tried putting values around the table select columns statement

     

    Table = VALUES ( SELECTCOLUMNS ( ..................... ) ) 

    • iamprajot's avatar
      iamprajot
      Icon for Responsive Resident rankResponsive Resident
      8 years ago
      I know all these and it works if we just need 1 column - Inside Values we cannot put SELECTCOLUMNS if SELECTCOLUMNS is having multiple columns
      BUT the catch is if we need multiple columns, for eg, 1 unique column like OrderID and other columns tat contains aggregates.
      TableName,
      "Column1", Values(OrderID),
      "Column1", SUM(Price)
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi iamprajot,

         

        1. Extract more than one column.

        Table = ALL('Table1'[Column1], 'Table1'[Column2])

        2. Formula for your scenario.

        Table = summarize('table1', 'table1'[OrderID], "sum", sum('table1'[Price])

        Please give them a try.

         

        Best Regards,

        Dale