create a new table with distinct values of one column, and the value of one other column

[DavidWaters100]

Hi,

I am struggling to achieve something which seems quite simple!

I want to create a new table using DAX with distinct values of one column, and the value of one other column - in the original table there are multiple entries, but each has two different references which are the same of each row.  See below example:

Original table

Ref1  Ref2  Amount

CDD  ROO    34

CDD  ROO    567

ABB  TEE        467

ABB  TEE        45

New Table

Ref1  Ref2

CDD  ROO

ABB  TEE

I can create the first column just using DISTINCT, but can't seem to return the other value.  I tried LOOKUPVALUE but it returned blanks!

Any help greatly appreciated.

Thanks

David

[Greg_Deckler · Accepted solution]

Table = DISTINCT(SELECTCOLUMNS('Table',"Ref1",[Ref1],"Ref2",[Ref2]))

[DavidWaters100]

Thank you very much - both answers I have accepted as solutions!

[FrankAT · Accepted solution]

Hi @DavidWaters100 ,

see figure:

Regards FrankAT

[Greg_Deckler · Accepted solution]

Table = DISTINCT(SELECTCOLUMNS('Table',"Ref1",[Ref1],"Ref2",[Ref2]))