DAX Distinct on Single Column

[Anthony_G1]

Hello,

I'm struggling with trying to do something in DAX that's very easy in Power Query, however I need to do it in DAX.

Basically, I have a table like below:

COL1	COL2
AAA	123
AAA	456
BBB	789

I want to do a distinct based just on COL1, so the output I'm looking for is

COL1	COL2
AAA	123
BBB	789

I tried using different combinations of FILTER, CALCULATETABLE, and DISTINCT, but everything I've tried is giving me errors. 

Thank you in advance to anyone who can help!

[Mariusz · Accepted solution]

Hi @Anthony_G1 

If you need to return a table then try this.

Table 2 = 
ADDCOLUMNS(
    VALUES( 'Table'[COL1] ),
    "COL2", CALCULATE( MIN( 'Table'[COL2] ) )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anthony_G1]

@Mariusz 

Thank you Mariusz. I apologize but I should have used a better example. My table actually has five columns, and I tried extrapolating the answer you provided to generate a table that has more than the two columns but I'm unable to.

So if Table 1 has 

COL1	COL2	COL3	COL4	COL5
AAA	123	qwerty	yti	poui
AAA	456	asdf	fghj	lkjh
BBB	789	zxcv	cvbn	mnbv

How can I get:

COL1	COL2	COL3	COL4	COL5
AAA	123	qwerty	yti	poui
BBB	789	zxcv	cvbn	mnbv

Thank you again for your help. Sorry again for my poor first example.

[Anthony_G1]

@Mariusz Sorry again but I figured it out!

Table 2 =

ADDCOLUMNS(

VALUES('Table'[Column1]),

"Column2", CALCULATE( MIN( 'Table'[Column2] ) ),

"Column3", CALCULATE( MIN( 'Table'[Column3] ) ),

"Column4", CALCULATE( MIN( 'Table'[Column4] ) ),

"Column5", CALCULATE( MIN( 'Table'[Column5] ) )

)

Thanks again!