1

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 as I am trying to call this from SSRS?

Thanks!

Julaayi
  • 403
  • 2
  • 8
  • 23

2 Answers2

0

Look at: https://www.sqlbi.com/articles/introducing-summarizecolumns/

You switch from "group by" columns to "summary" columns by convention in the argument list to SUMMARIZECOLUMNS.

EG:

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", sum([Fact_Sales_Count])
)
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks David! I tried adding "sum" to the query and still seeing duplicates. I am creating a shared dataset and trying to call specific fields in each report from the above query. – Julaayi Jan 13 '20 at 16:44
-1

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]))
Julaayi
  • 403
  • 2
  • 8
  • 23
  • Hello Jan, sorry, but if I run your query exactly but with different names, of course, I have this exception: Unhandled exception. System.Data.OleDb.OleDbException (0x80004005): SELECTCOLUMNS except a column name as argument number. And running your second query with the filters, it returns also a syntax error. – RobyB Sep 15 '21 at 07:48
  • Please test your answer, and edit it accordingly. Thank you :) – RobyB Sep 15 '21 at 10:11