Forum Discussion

Abhaykumar's avatar
Abhaykumar
Microsoft Employee
10 years ago
Solved

Getting distinct values for multiple columns

Is there a way to get distinct values for multiple columns? Specifically, I want to replicate the following SQL query into PowerBI to create a new table:   SELECT DISTINCT Col1, Col2, Col3 FROM Tab...
  • ankitpatira's avatar
    ankitpatira
    10 years ago

    Abhaykumar You can also select individual columns in query editor and click Remove Duplicates.

     

  • achinm45's avatar
    achinm45
    10 years ago

    Hi Abhaykumar,

     

    Use DAX and in DAX the function SUMMARIZE().

    Summarize will pull out distinct values from columns.

     

    So your resultset will be new table from Summarize

    Table_Output =  Summarize(Table_IN,Col1,Col2,Col3)

     

    I hope it helps !

     

    BR,

    Achin

nmakani's avatar
nmakani
Regular Visitor
4 years ago

Hi Brian, 

 

I have a similar use case  - were you able to figure this out?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Sorry, I can't remember what I ended up doing, and don't have access to that file anymore.  More than likely, I solved it via a PowerBI table function, rather than M code. 

 

I remember some situations where I would create a new table that was a union of two other tables, and then create another table that just references that union table (just one column), and somehow making my relationships off of this "3rd leg" table worked without the ambguity errors that happen when using that "in-between" table.  Can't remember if that was my fix for this situation, but you might give that a try.  It's some kind of loophole (at least it is in my mind; I'm sure someone smarter has a techncial explanation for why that works).