Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Countrows measurement assistance

Hi, 

 

I am hoping someone can help me create a measurement that counts the number of orders that are shipped late. 

 

Here is my current measurement.  I know why this is not correct but cannot figure out how to count late our orders.

 

Does Not Meet SLA =
SUMX (
    VALUES ( PARTSSHIPPINGKPI_VW[CUSTOMER ORDER NUMBER] ),
    VAR vHeader = PARTSSHIPPINGKPI_VW[CUSTOMER ORDER NUMBER]
    VAR vCount =
        COUNTROWS (
            FILTER (
                PARTSSHIPPINGKPI_VW,
                PARTSSHIPPINGKPI_VW[CUSTOMER ORDER NUMBER] = vHeader
                    && PARTSSHIPPINGKPI_VW[SLA] = "Does Not Meet SLA"
                    && PARTSSHIPPINGKPI_VW[NEW ITEM] = 0
            )
        )
    RETURN
        IF ( NOT ISBLANK ( vCount ), 1 )
)
 
The table below shows an example of a customer order I do not want to count but the above measurement is currently counting.  Here are the requirements for the measurement:
  • Count the number of Customer Order Numbers that are Late
    • A "late" order will have SLA = Does Not Meet SLA
    • If one (or more) of the Customer Order Lines is late, the entire Customer Order Number is considered late. 
    • If New Item = 1 on ANY of the customer order lines do count the order as late (skip the order) 
Thanks for the help!
CUSTOMER ORDER NUMBERCUSTOMER ORDER LINESITEMNEW ITEMBUSINESS DAYSSLAMeets SLADoes Not Meet SLA
CP000224171529A414202Meets SLA1 
CP000224172529A414302Meets SLA1 
CP000224173526G4134032Does Not Meet SLA 1
CP000224174526G4135032Does Not Meet SLA 1
CP000224175526G4137032Does Not Meet SLA 1
CP000224176526G4139032Does Not Meet SLA 1
CP000224177526G4140032Does Not Meet SLA 1
CP000224178526G4133132Does Not Meet SLA  
CP000224179526G4136132Does Not Meet SLA  
CP0002241710526A2016BA032Does Not Meet SLA 1
CP0002241711A32-160020032Does Not Meet SLA 1
 
"DAX"
COUNTROWS
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous,

    You can try to use the following measure formulas to get the order numbers that are shipped late based on the conditions:

    formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [CUSTOMER ORDER NUMBER],
        [CUSTOMER ORDER LINES],
        [NEW ITEM],
        [SLA]
    )
VAR NewOrders =
    SUMMARIZE ( FILTER ( summary, [NEW ITEM] = 1 ), [CUSTOMER ORDER NUMBER] )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                summary,
                NOT ( [CUSTOMER ORDER NUMBER] IN NewOrders )
                    && [SLA] = "Does Not Meet SLA"
            ),
            [CUSTOMER ORDER NUMBER]
        )
    )

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous,

    You can try to use the following measure formulas to get the order numbers that are shipped late based on the conditions:

    formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [CUSTOMER ORDER NUMBER],
        [CUSTOMER ORDER LINES],
        [NEW ITEM],
        [SLA]
    )
VAR NewOrders =
    SUMMARIZE ( FILTER ( summary, [NEW ITEM] = 1 ), [CUSTOMER ORDER NUMBER] )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                summary,
                NOT ( [CUSTOMER ORDER NUMBER] IN NewOrders )
                    && [SLA] = "Does Not Meet SLA"
            ),
            [CUSTOMER ORDER NUMBER]
        )
    )

    Regards,

    Xiaoxin Sheng