Forum Discussion
DAX Distinct on Single Column
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!
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
3 Replies
- Mariusz
Community Champion
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.
LinkedInThank 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.
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!
