Nested GROUP BY Problem

[mmalexander]

Hi,
I'm looking to write a nested Group By DAX statement to give me a table that shows the last delivery for each product item.

Each delivery can have multiple items and each item can be included multiple times in any delivery. This is why I need to do a nested Group By, otherwise I multiply up on joins, if I only Group on the last delivery (It may have more than 1 line for the product item). I need the table to have the delivery number, the product item, and the delivery line number, so I can then join on the delivery table and pull in other information, like customer, delivery date, price, currency, etc.

See below for an example of the delivery table, and what output I need to see in the nested group table

Delivery Table
Delivery Number	Product Item	Delivery Line Number	Quantity	Price
10182	MC-700255-99	0	3	600.00
10183	MC-M06026114	0	64	97.10
10183	MC-M06026115	1	72	181.90
10184	M06026169-r00	0	10	0.00
10185	MC-710155-012	0	1	9,396.11
10185	MC-710255-0122	1	1	16,741.60
10185	MC-710155-012	2	1	9,396.11
10185	MC-700255-99	3	1	200.00
10185	MC-711X55-000FRT	4	2	15.00
10186	MC-710155-012	0	2	9,396.11
10186	MC-710255-0124	1	2	16,597.55
10186	MC-700155-99	2	2	140.00
10186	MC-700155-99	3	2	140.00
10186	MC-711X55-000FRT	4	4	15.00
10187	MC-710255-0114	0	1	16,972.75
10187	MC-710155-012	1	1	9,396.11
10187	MC-710255-0114	2	1	16,972.75

Nested Group Output
Product Item	Delivery Number (Max)	Delivery Line Number (Max)
MC-M06026114	10183	0
MC-M06026115	10183	1
M06026169-r00	10184	0
MC-710255-0122	10185	1
MC-700255-99	10185	3
MC-710255-0124	10186	1
MC-700155-99	10186	3
MC-711X55-000FRT	10186	4
MC-710155-012	10187	1
MC-710255-0114	10187	2

When I try this DAX code, for a nested Group By, it multiplies up the Product Item for each unique delivery number/line number combination

Nested GroupBy = GROUPBY(
GROUPBY(Delivery_Table,
Delivery_Table[Product_Item],
Delivery_Table[Delivery_Num],
"Max Line Number",
MAXX(CURRENTGROUP(), Delivery_Table[Line_Number])),
Delivery_Table[Product_Item],
[Max Line Number],
"Max Del Num",
MAXX(CURRENTGROUP(), Delivery_Table[Delivery_Num]))

Example MC-700255-99 Would return twice in the table, because of the combination of delivery number/line number being unique

Product Item	Delivery Number	Delivery Line Number
10182	MC-700255-99	0
10185	MC-700255-99	3

I can do this in a SQL query, by nesting the Group By queries and joining them on the Delivery Table, but I'm struggling to replicate this in Power BI. Any help would be appreciated.

Thanks in advance!

[v-yiruan-msft · Accepted solution]

Hi @mmalexander ,

You can create two measures as below to get it, please find the details in the attachment.

Delivery Number (Max) = 
VAR _pitem =
    SELECTEDVALUE ( 'Delivery'[Product Item] )
RETURN
    CALCULATE (
        MAX ( 'Delivery'[Delivery Number] ),
        FILTER ( ALLSELECTED ( 'Delivery' ), 'Delivery'[Product Item] = _pitem )
    )

Delivery Line Number (Max) = 
VAR _pitem =
    SELECTEDVALUE ( 'Delivery'[Product Item] )
RETURN
    CALCULATE (
        MAX ( 'Delivery'[Delivery Line Number] ),
        FILTER (
            ALLSELECTED ( 'Delivery' ),
            'Delivery'[Product Item] = _pitem
                && 'Delivery'[Delivery Number] = [Delivery Number (Max)]
        )
    )

Best Regards

[mmalexander]

Hi v-yiruan-msft,

This works well, thanks for your help.

Regards,

Mark