topic Re: Dax filter of all rows based on max value - SQL equivalent in DAX Commands and Tips

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

jswartz_09 — Sat, 07 Sep 2024 17:37:26 GMT

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

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

lbendlin — Sat, 07 Sep 2024 20:40:17 GMT

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.

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

jswartz_09 — Fri, 13 Sep 2024 21:10:05 GMT

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

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

lbendlin — Fri, 13 Sep 2024 21:51:01 GMT

A calculated column that you can use for filtering:

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

jswartz_09 — Fri, 13 Sep 2024 22:26:33 GMT

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

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

lbendlin — Fri, 13 Sep 2024 22:34:22 GMT

What do you not like about this way?

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

jswartz_09 — Fri, 13 Sep 2024 22:51:30 GMT

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

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

lbendlin — Fri, 13 Sep 2024 22:53:26 GMT

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.

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

SachinNandanwar — Fri, 13 Sep 2024 23:22:34 GMT

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.

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

lbendlin — Sat, 14 Sep 2024 01:00:06 GMT

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.

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

SachinNandanwar — Sun, 15 Sep 2024 11:06:02 GMT

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 ?

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

lbendlin — Sat, 14 Sep 2024 20:45:27 GMT

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-Forum/ba-p/963216

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/1447523

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

SachinNandanwar — Sun, 15 Sep 2024 00:13:55 GMT

Its related to the issue in this thread.

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

jswartz_09 — Tue, 17 Sep 2024 00:46:02 GMT

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.

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

Anonymous — Mon, 07 Oct 2024 06:12:31 GMT

Hi jswartz_09 ,

Have a good day. Has your problem been solved?

Best Regards,

Wearsky