Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OneWithQuestion
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
camargos88
Community Champion
Community Champion

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
camargos88
Community Champion
Community Champion

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!



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!



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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.