Forum Discussion

bradc's avatar
bradc
Frequent Visitor
1 year ago
Solved

Calculate Filters

Hi All,   I'm really struggling with Calculate and Filters.  I'm sure this is easy, but the solution is eluding me.   Basically I need to find the MAX Item number with a date, where the condition...
Need Help
  • bradc's avatar
    bradc
    1 year ago

    I cracked the code!

    This is what I needed:

    CALCULATE(
    MAX(Table[Date]),
    FILTER(
        'Table',
        Table[Condition] = FALSE && Table[Item] = EARLIER(Table[Item])
    )
)

    Thank you both sevenhills and danextian  for your assistance. 

    It is much apprecited.

danextian's avatar
danextian
Super User
1 year ago

Hi bradc 

try either:

MAXX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                'table',
                NOT ( ISBLANK ( 'table'[date] ) )
                    && 'table'[condition] = FALSE
            ),
            'table'[item]
        ),
        "Number", 'table'[number]
    ),
    [Number]
)

 

CALCULATE (
    MAXX ( SUMMARIZE ( 'table', 'table'[item], 'table'[number] ), [number] ),
    NOT ( ISBLANK ( 'table'[date] ) ),
    'table'[condition] = FALSE
)

 

  • bradc's avatar
    bradc
    Frequent Visitor
    1 year ago

    Thanks for your reply danextian 

     

    Again I'm struggling to apply your suggestion to my specific problem.
    The closest I believ I have got is:

    CALCULATE(
    MAX(Table[Date]),
    ALLEXCEPT('Table', Table[Item]),
    FILTER('Table', Table[Condition] = FALSE)
)

    This results in 

    The result required is

    for item 15859 - 17/10/2024

    for item 15860 - 12/09/2024 (Ignoring 9/10/2024 as it is not a false condition), and

    For item 15861 - Blank() as it doesn't have a date yet.  I will look up a date from another table in this case.

     

    Again, any assistance is much appreciated.

    • danextian's avatar
      danextian
      Super User
      1 year ago

      can you please post a workable sample data, not an image. it will be easier for us to test the proposed solution than imagine the results in our heads