Dax filter of all rows based on max value - SQL equivalent

[jswartz_09]

Ok so currently I have a table with a multitude of columns and rows. What I want to do is filter all the rows based on the maximum value of one column OrderID for the CustomerID. 

Table looks like This:

CustomerID	CustName	OrderID	OrderDate	OrderStatus	OrderReceivedDate
1	John	1114	01/01/2024	Complete	01/08/2024
1	John	1115	01/10/2024	Complete	01/15/2024
2	Dan	2212	03/05/2024	Shipped
2	Dan	3457	03/06/2024	Shipped
2	Dan	4875	03/08/2024	Shipped
3	Bob	1112	12/17/2023	Complete	12/26/2023
3	Bob	5555	03/10/2024	Pending
4	Frank	1110	12/10/2023	Complete	12/15/2023
4	Frank	6758	03/18/2024	Pending
4	Frank	6759	03/18/2024	Shipped

Basically in SQL what I am trying to accomplish would be written like the following

SELECT *

FROM myTable a1

where a1.OrderID = (select max(b1.OrderID)

                                from myTable b1

                                where b1.CustomerID = a1.CustomerID)

Output would be:

CustomerID	CustName	OrderID	OrderDate	OrderStatus	OrderReceivedDate
1	John	1115	01/10/2024	Complete	01/15/2024
2	Dan	4875	03/08/2024	Shipped
3	Bob	5555	03/10/2024	Pending
4	Frank	6759	03/18/2024	Shipped

Currently I can achieve this by the following Dax measure: 

isMax = IF(myTable[OrderID] = CALCULATE(MAX(myTable[OrderID],AllEXCEPT(myTable[CustomerID])), 1, 0)

then I filter that measure to 1. 

I feel like there has to be another way to do this right? Maybe creating a new table to do this? 

Thank you

[Anonymous]

Hi @jswartz_09 ,

Have a good day. Has your problem been solved?

Best Regards,

Wearsky

[jswartz_09]

Now now don't you two fight over my question ;). These are excellent solutions. @SachinNandanwar  I was wondering if you could explain how your solution works in more detail. I'm trying to learn more Dax.

[SachinNandanwar]

How about this ?

Customer_Measure_Id =
VAR _OrderId =
    MAX ( 'Tbl'[OrderID] )
VAR _MaxOrder =
    SUMMARIZE (
        ALL ( 'Tbl' ),
        'Tbl'[CustomerID],
        "OrderId", MAX ( 'Tbl'[OrderID] )
    )
VAR FilterVar =
    FILTER ( _MaxOrder, [OrderId] = _OrderId )
RETURN
    MAXX ( FilterVar, 'Tbl'[CustomerID] )

Just replace CustomerId column with Customer_Measure_Id  in the display. 

[lbendlin]

Multiple red flags.

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

Again - use DAX Studio to measure for yourself. No need to guess.

[SachinNandanwar]

Thats interesting.Thanks for pointing that out.

the article you quoted doesnt necessarily states that using SUMMARIZE are red flags but explains the limitations of using it wrt to extended columns and suggests usage of ADDCOLUMNS with CALCULATE.

I found a better explaination here.

https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns

The best practice proposed is to use

ADDCOLUMNS(
SUMMARIZE( <table>, <group by column> ),
<column_name>, CALCULATE( <expression> )
)

which I agree. But in my solution the change isnt working. I changed the DAX from this

VAR _MaxOrder = SUMMARIZE (
ALL ( Tbl ),
'Tbl'[CustomerID],
"Order_Id", ( MAX ( 'Tbl'[OrderID] ) )
)

to this

VAR _MaxOrder = ADDCOLUMNS (
SUMMARIZE ( ALL ( Tbl ), 'Tbl'[CustomerID] ),
"Order_Id", CALCULATE ( MAX ( 'Tbl'[OrderID] ) )
)

and it doesnt work.Any thoughts ?

[lbendlin]

@SachinNandanwar  Not sure what your question is.  Maybe open a new thread?  

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[SachinNandanwar]

Its related to the issue in this thread.

[lbendlin]

Since this data is immutable you can create a calculated column, or do this in Power Query.  (or even in SQL if you want)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBCsIwEER/RXoWurtpmnhV8eBJ8Fh6qDTYoqZF/H/csNHSWtEQyO7whmFSFAkmy2TfNZ4fRMz4AUz5ElBYNt2tv7qHi7oVvVxOjVoAhHkj6sFILG2r4CPCsIBKQb98x6bte1fztJjQKtMm0vlvOrNGR9p+oxVP6+4kDYIVKUUTaDVuwDrloo99mo+kDM0PztetP79Tgra7V/4iORBzYD5Hfkp9OHOjbUyy/yQxv5ryo/7lEw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, CustName = _t, OrderID = _t, OrderDate = _t, OrderStatus = _t, OrderReceivedDate = _t]),
    #"Grouped Rows" = Table.Group(Source, {"CustomerID"}, {{"Rows", each _, type table [CustomerID=nullable text, CustName=nullable text, OrderID=nullable text, OrderDate=nullable text, OrderStatus=nullable text, OrderReceivedDate=nullable text]}, {"Max Order", each List.Max([OrderID]), type nullable text}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"CustName", "OrderID", "OrderDate", "OrderStatus", "OrderReceivedDate"}, {"CustName", "OrderID", "OrderDate", "OrderStatus", "OrderReceivedDate"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Rows", each ([OrderID] = [Max Order])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Order"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[jswartz_09]

This is nice for power query, but how would you do this in Dax?

[lbendlin]

A calculated column that you can use for filtering:

[jswartz_09]

Yeah that's what I've been doing. Sorry I'm being difficult but is there another way? Or better way?

[lbendlin]

What do you not like about this way?

[jswartz_09]

Nothing I don't like. Just wondering if there are other ways

[lbendlin]

There are, but most likely they will perform worse  (depends on the size of your table too).

If in doubt - use DAX Studio to measure query performance and examine the query plan.