cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

SUMMARIZE to get MIN and MAX of a group?

I am running into issues with what seems like it should be a very simple thing:

MINOrder = CALCULATE(MINX(SUMMARIZE('Table','Table'[CustomerID],'Table'[OrderDate]),MIN('Table'[OrderDate])))

sample file https://1drv.ms/u/s!Avt4VtHjUwp93l9MiWDpv2AZAaja?e=KBc9pm
I want to find the MIN and MAX oder date per CustomerID.

I am trying to do this with a measure and using SUMMARIZE, but I can't return the max and min while also displaying each order ID.

What I want to see is something like this where MAXOrder and MINOrder are measures
Screenshot 2020-10-26 111925.png

 
 
MINOrder = CALCULATE(MINX(SUMMARIZE('Table','Table'[CustomerID],'Table'[OrderDate]),MIN('Table'[OrderDate])))

Am I not getting out far enough to return all the rows per CustomerID?  Is the SUMMARIZE not getting ALL rows per CustomerID but instead returning a single row for each row it is evaluating?

So if it is on row 2 instead of returning ALL rows for Customer 3 it is only returning the single row it is on?


Sample Data
CustomerIDOrderIDOrderDate
3141/14/2020
3151/15/2020
3161/16/2020
3171/17/2020
3201/20/2020
3231/23/2020
511/1/2020
521/2/2020
531/3/2020
541/4/2020
551/5/2020
581/8/2020
5111/11/2020
5121/12/2020
5131/13/2020
5191/19/2020
5221/22/2020
4561/6/2020
4571/7/2020
4591/9/2020
45101/10/2020
45181/18/2020
45211/21/2020


 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Hi @OneWithQuestion ,

 

Try these measures:

_MaxOrderDate = CALCULATE(MAX(Table_A[OrderDate]), ALLEXCEPT(Table_A, Table_A[CustomerID]))
_MinOrderDate = CALCULATE(MIN(Table_A[OrderDate]), ALLEXCEPT(Table_A, Table_A[CustomerID]))


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

Proud to be a Super User!



View solution in original post

@OneWithQuestion ,

 

The ALLEXCEPT function ignores all filters, keeping only those you leave in the parameters, your case Customer ID.



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

Proud to be a Super User!



View solution in original post

7 REPLIES 7
Super User III
Super User III

Hi @OneWithQuestion ,

 

Try these measures:

_MaxOrderDate = CALCULATE(MAX(Table_A[OrderDate]), ALLEXCEPT(Table_A, Table_A[CustomerID]))
_MinOrderDate = CALCULATE(MIN(Table_A[OrderDate]), ALLEXCEPT(Table_A, Table_A[CustomerID]))


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

Proud to be a Super User!



View solution in original post

Ah thank you.

So that worked because it discarded all row context EXCEPT the CustomerID.  

Basically, it generated a new table that contained only rows with the CustomerID matching?


@OneWithQuestion ,

 

The ALLEXCEPT function ignores all filters, keeping only those you leave in the parameters, your case Customer ID.



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

Proud to be a Super User!



View solution in original post

If for some reason I WANTED to use SUMMARIZE, could I?


@OneWithQuestion ,

Yes,

Summarize will group the rows based on what you want. However your code will be slower, once it creates a virtual table with the values and you still need to "query" the min value per customer.



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

Proud to be a Super User!



How can I get that to work with a SUMMARIZE, I am still playing around with that (because I want to understand it better) but I can't get it to work.

@OneWithQuestion ,

 

A good example is when you need to group before calculate something (sum only the max/min/avg of values)....

 

This is a good article about summarize, it has changed over the years:

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/#



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

Proud to be a Super User!



Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

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