Countrows measurement assistance

[Anonymous]

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 NUMBER	CUSTOMER ORDER LINES	ITEM	NEW ITEM	BUSINESS DAYS	SLA	Meets SLA	Does Not Meet SLA
CP00022417	1	529A4142	0	2	Meets SLA	1
CP00022417	2	529A4143	0	2	Meets SLA	1
CP00022417	3	526G4134	0	32	Does Not Meet SLA		1
CP00022417	4	526G4135	0	32	Does Not Meet SLA		1
CP00022417	5	526G4137	0	32	Does Not Meet SLA		1
CP00022417	6	526G4139	0	32	Does Not Meet SLA		1
CP00022417	7	526G4140	0	32	Does Not Meet SLA		1
CP00022417	8	526G4133	1	32	Does Not Meet SLA
CP00022417	9	526G4136	1	32	Does Not Meet SLA
CP00022417	10	526A2016BA	0	32	Does Not Meet SLA		1
CP00022417	11	A32-160020	0	32	Does Not Meet SLA		1

[v-shex-msft · Accepted solution]

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