Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filtering rows by aggregation

Hi guys,   I have a big problem with finding a solution. We want to reduce the number of shipments to <= 2 per month, per part number. I want to count rows where it was 3rd or more shipment of a sp...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    Please try this code to create a measure.

    Count =
VAR _ADD =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "YearMonth",
            YEAR ( 'Table'[Date] ) * 100
                + MONTH ( 'Table'[Date] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _ADD,
        "Flag",
            RANKX (
                FILTER (
                    _ADD,
                    [Part Number] = EARLIER ( [Part Number] )
                        && [YearMonth] = EARLIER ( [YearMonth] )
                ),
                [Date],
                ,
                ASC,
                DENSE
            )
    )
RETURN
    COUNTAX (
        FILTER ( _ADD1, [Part Number] = MAX ( 'Table'[Part Number] ) && [Flag] > 2 ),
        [Flag]
    )

     Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

EDIT. I want to count excesive shipments in total (without splitting to part numbers) and this need to work with Supplier slicer.