topic Re: Measure value for first item only in DAX Commands and Tips

Measure value for first item only

IgorM — Sat, 30 Sep 2023 19:19:20 GMT

Hi,

I need to create a measure that returns value for the first row with a given order.

The source data is as follows:

SOURCE DATA
Order No	DN#	Order Total Weight
1383130672	MK0Q3LL/A	50
1383130672	MK0U3LL/A	50
1383130672	MMYQ3LL/A	50
1383130672	MMYW3LL/A	50
QAD5299674	MGPL3LL/A	48
QAD5299674	MMFK3LL/A	48
QAD5496134	MPQ03LL/A	12
QAD5503260	MQ6U3VC/A	38
QAD5540852	MGPH3LL/A	25

The resulting report (in Excel) has to look like this

REPORT
Order No	DN#	Total Weight	Order Total Weight
1383130672	MK0Q3LL/A	50	50
1383130672	MK0U3LL/A	50
1383130672	MMYQ3LL/A	50
1383130672	MMYW3LL/A	50
QAD5299674	MGPL3LL/A	48	48
QAD5299674	MMFK3LL/A	48
QAD5496134	MPQ03LL/A	12	12
QAD5503260	MQ6U3VC/A	38	38
QAD5540852	MGPH3LL/A	25	25

The Total Weight column is simply =MAX([Order Total Weight]). How can I get values as shown in column Order Total Weight?

Re: Measure value for first item only

lbendlin — Sun, 01 Oct 2023 22:00:26 GMT

Please define "First Row" - there is no built-in concept for that in Power Query. Do you mean sorted by DN#?

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc1BCoMwEIXhu8xa6GQmMyZLabEFEzALLSK5/zWqllio4u7Bx8+bZzDs2DBqTVBB7DBxCLdm2YKQq6MPlx6n6z5O7z9PzUPIe63t6s8+FLfuxGPbnbj1anjzPmFxQ7sLMimunnTg8b45/3qx6IS+/6/Sk0DOHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, #"DN#" = _t, #"Order Total Weight" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Total Weight", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Order Total Weight", "Total Weight"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Order Total weight", each if [Index]>0 and #"Added Index"[Order No]{[Index]-1}=[Order No] then null else [Total Weight]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Order No", "DN#", "Total Weight", "Order Total weight"}) in #"Removed Other 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".

Re: Measure value for first item only

IgorM — Mon, 02 Oct 2023 13:37:20 GMT

Hi Ibendlin,

thank you for a prompt response.

However, what I was actually looking for a DAX solution, not Power Query.

Regarding your question about "first row", please note that by "first row" I mean the first row/occurence of a given Order No. In an Excel pivot table, when you place an item in the Rows section, Excel (by default) does not repeat the item labels when data for a given item is displayed in more than one row.

I need to replicate exactly the same concept, but for an item placed in the Values section (show the item's value only in the first row with data for that item). I hope I have made myself clear now.

Kind regards,

Re: Measure value for first item only

lbendlin — Mon, 02 Oct 2023 15:35:11 GMT

I mean the first row/occurence of a given Order No

DAX has no concept of that "first occurrence" - you need to bring your own index column (or other logic), same like I showed for Power Query.

Re: Measure value for first item only

IgorM — Mon, 09 Oct 2023 07:55:33 GMT

Hi Ibendlin,

I realise that DAX has no concept of row ordering but for the purpose of this query to identify the first row for a given Order No I was thinking of using the RANK function.

Regards,

Re: Measure value for first item only

lbendlin — Mon, 09 Oct 2023 12:14:22 GMT

There is also a ROWNUMBER function but the same caveat applies - it is non-deterministic.