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 specific part number in a specific month. in the example below rows 3 and 6 should be counted. I can't use RANKX because it's DirectQuery.  I tried to create calculated grouped table but in table I have dates and I couldn't use YearMonth column from calendar table (related). Do you have any ideas? 

 

RowSupplierPart NumberDateTracking Number
10010A6/10/2201
20010A6/11/2202
30010A6/12/2203
40020B6/13/2204
50020B6/14/2205
60030B6/15/2206
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.

     

3 Replies

  • 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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      It works. Thank you.