Forum Discussion
Getting distinct values for multiple columns
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
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
I am trying to create a table that shows the person's unique code, department and name. I am using the Summarize function and am getting duplicates. How do I exclude the duplicate that does not get associated with a department?
Table = Summarize('MO YTD Jan - June Revenue','MO YTD Jan - June Revenue'[Employee Code],'MO YTD Jan - June Revenue'[Department Name])Thank you!KimIf you do it in DAX, just be aware that the full table must be loaded into the data model. Doing it in the query-editor using M would (mostly) fold back to the server, meaning that the SQL-server does the extraction and returns only the results to PBI to be loaded.
So if you need to load the full table anyway, DAX would probably be faster.
You can also wrap a DISTINCT(SELECTCOLUMNS(table, "col1name", Col1fromtable, table, "col2name", Col2fromtable)
How can I copy distinct values from multiple columns into ONE single colum?
The shown above always created multiple columns into which the code copies the distinct values from each column.
Thanks!
Despite the existence of many solutions in Power BI (Query Editor, DAX, etc), this seams to be the most elegante solution.
Thants for your hint achinm45Hi achinm45,
Receiving the following error when using your suggested function (below; names anonymized):
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
SUMMARIZE(table,[col1],[col2],[col3])Thank You!I'm getting the same thing. Did you find a solution?
perfect!
