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.

sevenhills's avatar
sevenhills
Super User
1 year ago

Try this

 

Max of number with conditions of all data.

 

MaxItemNumberWithDate =
CALCULATE(MAX(Table[Number]),  
  Filter(ALL(Table), 
        NOT ISBLANK(Table[Date]) <> Blank() && Table[Condition]=False()  
  )
)

 

 

Or if you want max per each item then, Max of number of with conditions of all data for each item:

 

 

MaxItemNumberWithDate =

VAR _sel = SELECTEDVALUE(Table[Item])

RETURN IF ( HASONEVALUE (Table[Item]), 
CALCULATE(MAX(Table[Number]),  
  Filter(ALL(Table), 
        NOT ISBLANK(Table[Date]) <> Blank() && Table[Condition]=False()  
        && Table[Item] = _sel
  )
) 
, BLANK () // No max 
)

 

 

 

 

 

 

bradc's avatar
bradc
Frequent Visitor
1 year ago

Thanks for your response sevenhills 

 

I don't think that quite works as I'm trying to calculate the max for each item in the table, so I think the 'ALLEXCEPT' filter is quite important.

 

Below is what I tried, but still no success I'm afraid.

 

Column = 
VAR MaxItemNumberWithDate = 
	CALCULATE(MAX(Table[Number]),
	ALLEXCEPT(Table, Table[Item]),
	Table[Date] <> Blank() && Table [Condition] <> TRUE())
        )
RETURN Table[Number] = MaxItemNumberWithDate +1