How to Add a New Table by Using DISTINCT with Multiple Columns

Posted on by Summa Lai

The SELECTCOLUMNS function simply takes some column/columns from another table and create a new table from them.

For example, you can take only one column, Revenue, The syntax of SELECTCOLUMNS is simple:

Smaller table = SELECTCOLUMNS(Table1;”This day revenue”;[Revenue])


It is the new table name, column name in the new table, column name in the original table, column name in the new table, column name in the original table…)
If you need unique value only, not all rows, you may use DISTINCT.

For Example:


DISTINCT(
SELECTCOLUMNS('Dim_Products',
'Dim_Products'[SaleCode], 
'Dim_Products'[ProducttName],  
'Dim_TimeZone'[StartDate], 
'Dim_TimeZone'[StartTime], 
'Dim_TimeZone'[EndDate], 
'Dim_TimeZone'[EndTime], 
'Dim_TimeZone'[Variation]))

And, if we need to add a filter:


DISTINCT(
SELECTCOLUMNS(
FILTER('Dim_Products', 'Dim_Products'[SaleCode] = 123 && ('Dim_Products'[ProducttName] = "ABC" || 'Dim_Products'[ProducttName] = "XYZ" )),
'Dim_Products'[SaleCode], 
'Dim_Products'[ProducttName], 
'Dim_TimeZone'[StartDate], 
'Dim_TimeZone'[StartTime], 
'Dim_TimeZone'[EndDate], 
'Dim_TimeZone'[EndTime], 
'Dim_TimeZone'[Variation]))