Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

MAX Date per Material where Measure > 0

Hi, what I am trying to do is to get the maximum date by Material. I'm using datasets, tables with attributes, and tables with measures. 

 

DAX:

EVALUATE
SUMMARIZECOLUMNS (
    'Material'[MAT WRIN0],
    'Delivery Date'[DLVD Date],
    'Purchase Order'[PO Number],

    FILTER (
        VALUES ( 'Distributor'[DIST Country] ),
        ( 'Distributor'[DIST Country] = "Romania" )
    ),
    FILTER (
        VALUES ( 'Purchase Order'[PO Status Description] ),
        ( 'Purchase Order'[PO Status Description] = "Purchase order APPROVED" )
    ),

    "Purchase Order To Be Received Quantity", [Purchase Order To Be Received Quantity]
    )
ORDER BY 'Delivery Date'[DLVD Date]

 

Here is the data extract, and the final result should be like WRIN0 and Max DLVD Date (just one line per WRIN0).

NewUserHI21_0-1654768075161.png

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 

please use

 

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Material'[MAT WRIN0],
        'Delivery Date'[DLVD Date],
        'Purchase Order'[PO Number],
        FILTER (
            VALUES ( 'Distributor'[DIST Country] ),
            ( 'Distributor'[DIST Country] = "Romania" )
        ),
        FILTER (
            VALUES ( 'Purchase Order'[PO Status Description] ),
            ( 'Purchase Order'[PO Status Description] = "Purchase order APPROVED" )
        ),
        "Purchase Order To Be Received Quantity", [Purchase Order To Be Received Quantity]
    ),
    'Delivery Date'[DLVD Date]
        = CALCULATE (
            MAX ( 'Delivery Date'[DLVD Date] ),
            ALLEXCEPT ( 'Material', 'Material'[MAT WRIN0] )
        )
)
ORDER BY 'Delivery Date'[DLVD Date]

 

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @Anonymous 

please use

 

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Material'[MAT WRIN0],
        'Delivery Date'[DLVD Date],
        'Purchase Order'[PO Number],
        FILTER (
            VALUES ( 'Distributor'[DIST Country] ),
            ( 'Distributor'[DIST Country] = "Romania" )
        ),
        FILTER (
            VALUES ( 'Purchase Order'[PO Status Description] ),
            ( 'Purchase Order'[PO Status Description] = "Purchase order APPROVED" )
        ),
        "Purchase Order To Be Received Quantity", [Purchase Order To Be Received Quantity]
    ),
    'Delivery Date'[DLVD Date]
        = CALCULATE (
            MAX ( 'Delivery Date'[DLVD Date] ),
            ALLEXCEPT ( 'Material', 'Material'[MAT WRIN0] )
        )
)
ORDER BY 'Delivery Date'[DLVD Date]

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.