How to do DISTINCT on multiple columns in DAX query?

[Anonymous]

Hello Everyone!

I am pretty new to the DAX world. I am trying to do get distinct records on multiple columns in DAX query similar to the way I do in SQL. I tried joining two tables based on the model in the Query Designer which gave me the following query.

EVALUATE SUMMARIZECOLUMNS(
'Dim_Products'[SaleCode], 
'Dim_Products'[ProducttName],  
'Dim_TimeZone'[StartDate], 
'Dim_TimeZone'[StartTime], 
'Dim_TimeZone'[EndDate], 
'Dim_TimeZone'[EndTime], 
'Dim_TimeZone'[Variation],  
"Fact_Sales_Count", [Fact_Sales_Count]
)

Running the above is giving duplicate records. How do I just get distinct records from the query iteslf instead of choosing the 'Remove duplicates" option?

Thanks!

[kentyler]

My other suggest would be that you don't have to do everything in DAX (and often its better not to). This looks like it might be an operation that could be done as part of your load cycle, so you only load a table with distinct rows into DAX and don't have to transform it there.

[Anonymous]

Thanks @kentyler ! I was able to achieve what I needed. Just in case if this helps someone in future.

EVALUATE 
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:

EVALUATE 
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]))

[kentyler]

Could you post a sample of your data ? SUMMARIZE COLUMNS is supposed to return distinct rows. So if it's not that's curious.