Unique records in SELECTCOLUMNS

[iamprajot]

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.

[danextian]

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]

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]

have you tried putting values around the table select columns statement

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

[iamprajot]

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 · Accepted solution]

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

[MarkPalmberg]

Hi, @v-jiascu-msft .

I created a table that's a filtered version of one of my existing tables. Can I somehow reference this newly-created table in the summarize command to get only selected columns for the filtered table, or do I need to do a separate make table command to modify my filtered table? Here's the code to create the filtered table:

Household DQ Unit = 
    FILTER('Qualification Interactions', OR ('Qualification Interactions'[OUTCOME]="Disqualified for Business Unit", 'Qualification Interactions'[DISQUALIFIED FOR UI]=1) )

[iamprajot]

I think I was using summarize incorrectly, I did the same thing but didn't work and now it is working,
can you please provide similar alternative using SELECTCOLUMNS

[v-jiascu-msft]

Hi @iamprajot,

I'm afraid SELECTCOLUMNS wouldn't work in this scenario. Please refer to the definition of this function.

SELECTCOLUMNS(<table>, <name>, <scalar_expression> [, <name>, <scalar_expression>]…) 

Please mark the proper answer as solution if it helps.

Best Regards,

Dale