Forum Discussion

IgorM's avatar
IgorM
Frequent Visitor
2 years ago

Measure value for first item only

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 NoDN#Order Total Weight
1383130672MK0Q3LL/A50
1383130672MK0U3LL/A50
1383130672MMYQ3LL/A50
1383130672MMYW3LL/A50
QAD5299674MGPL3LL/A48
QAD5299674MMFK3LL/A48
QAD5496134MPQ03LL/A12
QAD5503260MQ6U3VC/A38
QAD5540852MGPH3LL/A25

 

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

REPORT
Order NoDN#Total WeightOrder Total Weight
1383130672MK0Q3LL/A5050
1383130672MK0U3LL/A50 
1383130672MMYQ3LL/A50 
1383130672MMYW3LL/A50 
QAD5299674MGPL3LL/A4848
QAD5299674MMFK3LL/A48 
QAD5496134MPQ03LL/A1212
QAD5503260MQ6U3VC/A3838
QAD5540852MGPH3LL/A2525

 

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

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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".

    • IgorM's avatar
      IgorM
      Frequent Visitor
      2 years ago

      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,

      IM

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

         

        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.