Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am running into issues with what seems like it should be a very simple thing:
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
CustomerID | OrderID | OrderDate |
3 | 14 | 1/14/2020 |
3 | 15 | 1/15/2020 |
3 | 16 | 1/16/2020 |
3 | 17 | 1/17/2020 |
3 | 20 | 1/20/2020 |
3 | 23 | 1/23/2020 |
5 | 1 | 1/1/2020 |
5 | 2 | 1/2/2020 |
5 | 3 | 1/3/2020 |
5 | 4 | 1/4/2020 |
5 | 5 | 1/5/2020 |
5 | 8 | 1/8/2020 |
5 | 11 | 1/11/2020 |
5 | 12 | 1/12/2020 |
5 | 13 | 1/13/2020 |
5 | 19 | 1/19/2020 |
5 | 22 | 1/22/2020 |
45 | 6 | 1/6/2020 |
45 | 7 | 1/7/2020 |
45 | 9 | 1/9/2020 |
45 | 10 | 1/10/2020 |
45 | 18 | 1/18/2020 |
45 | 21 | 1/21/2020 |
Solved! Go to Solution.
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]))
The ALLEXCEPT function ignores all filters, keeping only those you leave in the parameters, your case Customer ID.
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]))
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?
The ALLEXCEPT function ignores all filters, keeping only those you leave in the parameters, your case Customer ID.
If for some reason I WANTED to use SUMMARIZE, could I?
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.
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.
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/#
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |