Forum Discussion

mmalexander's avatar
mmalexander
Regular Visitor
2 years ago
Solved

Nested GROUP BY Problem

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 include...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    mmalexander
    Regular Visitor
    2 years ago

    Hi v-yiruan-msft,

    This works well, thanks for your help.

     

    Regards,

    Mark