cancel
Showing results for
Did you mean:
Highlighted
Microsoft

## DISTINCT over SUMMARIZE

I have a measure that follows the pattern as mentioned below

``````Test Adds =
CALCULATE (
SUMX (
DISTINCT ( SUMMARIZE ( Sales, Sales[Order Date], Sales[Order Number] ) ),
IF ( Sales[Sales Amount] > 0, 1, 0 )
)
)``````

I was wondering if I really need the DISTINCT over a SUMMARIZE call as the latter would already return unique combinations of order date and order number. Also, is there a better way to calculate the total number of orders for each day for which the sales amount > 0 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
I do not see the need for DISTINCT, SUMMARIZE already makes things distinct.

Maybe, but don't know your data to be sure. Should just be able to use COUNTROWS and FILTER the table returned from SUMMARIZE but not sure that is more efficient or not.

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

12 REPLIES 12
Highlighted
Super User IV
I do not see the need for DISTINCT, SUMMARIZE already makes things distinct.

Maybe, but don't know your data to be sure. Should just be able to use COUNTROWS and FILTER the table returned from SUMMARIZE but not sure that is more efficient or not.

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
Super User IV

I doubt you need to summarize there. Also, the formula should one of the two as per need

CALCULATE (
SUMX (
SUMMARIZE ( Sales, Sales[Order Date], Sales[Order Number],"_1",sumx ( Sales,if(Sales[Sales Amount] > 0, 1, 0 ) )),
[_1]
)
)

CALCULATE (
SUMX (
SUMMARIZE ( Sales, Sales[Order Date], Sales[Order Number],"_1",sum ( Sales[Sales Amount]) ),
if([_1] > 0, 1, 0 )
)
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User III

Hi,

Here's what i would do:

1. Create a Calendar Table and build a relationship from the Order date column of your Sales Table to the Date column of your Calendar Table.  Write calculated column formlas to extract various time dimensions such as Year, Month, Quarter
2. Drag time dimensions from the Calendar Table.
3. Write these measures

Total sales = SUM(Sales[Sales amount])

Orders which recorded sales = SUMX(FILTER(SUMMARIZE(Sales,Sales[Order Number],Calendar[Date],"Sale",[Total Sales]),[Sale]>0),[Sale])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Microsoft

@Ashish_MathurWhy would I want to do a sum of [Sales] measure ? I am just trying to get a count of number of orders for each day where sales > 0. I shouldn't have to do a sum on Sales , right ?

Highlighted
Microsoft

@Greg_Deckler  Yes, that's what I thought. I removed DISTINCT from the measure definition and the numbers seem to match. I am just trying to think of a case where I would need a DISTINCT over SUMMARIZE. It's impossible for me to test all possible combinations considering the dimensions I have in my dataset. In your experience, was there ever a case where you had to apply DISTINCT over a SUMMARIZE statement ? I am yet to do a performance comparison between both the measures.

Highlighted
Super User III

Hi,

Does this work?

=COUNTROWS(FILTER(SUMMARIZE(Sales,Sales[Order Number],Calendar[Date],"Sale",[Total Sales]),[Sale]>0))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Microsoft

@amitchandak  Would that have an performance improvement over my current metric definition ? If so, can you please help me understand how it would improve things ?

Highlighted
Microsoft

@Ashish_Mathur  Yes, that is matching with my current metric results, I have two other versions of the same metric, I am going to test the performance of each metric and get back

Highlighted
Super User IV

No on DISTINCT with SUMMARIZE, because when you SUMMARIZE, whatever your are grouping on will be distinct. DISTINCT will make sure that entire rows are distinct in a table versus just a particular column, but the fact that you are using SUMMARIZE ensures that the table rows are distinct, it's built into the grouping.

@Ashish_Mathur put into a formula what I was discussing with COUNTROWS and FILTER. Now, whether that is an improvment? You'd have to test it. See my 4 part series on Performance Tuning DAX: https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors