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 again sevenhills,

Let me apologise for my gaps in understanding.  I might revert back to what the actual goal of the process is, and maybe that will assist in me grasping the concepts better.

I have table with data that is structured as above.

What I need to find as a result are 2 things.
1.  Next Valid Number.  This is the Max Number with No Date.  To do this I use the below code.

 

NextValidNumber= 

VAR MaxNumberWithDate = 
	CALCULATE(MAX(Table[Number]),
	ALLEXCEPT(Table, Table[Item]),
	Table[Date] <> Blank())

RETURN Table[Number] = 
    MaxNumberWithDate +1

 

 

And 

2. Largest False Date.  This is where I struggle.  
What I have been doing is the below.

 

LargestFalseDate = 
	If(CALCULATE(MAX(Table[Date]), ALLEXCEPT('Table', Table[Item]))<>blank(),
		CALCULATE(MAX(Table[Date]), ALLEXCEPT('Table', Table[Item])),
		Related('OtherTable'[Other Date]))

 

But that results in the following results

and obviously for Item 15860, the result should be 12/09/2024.

If you could assist in helping me correctly calculate the Largest False Date it would be much appreciated.

  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago
    M1 = 
// This is a measure and not column
var _sel = SELECTEDVALUE( Table1[Item] ) // get the current Item 
var _Val1 = CALCULATE( MAX(Table1[Date]), FILTER(ALLSELECTED(Table1), Table1[Item] = _sel && Table1[Condition] = FALSE())) // Get the current item max date with condition as false

// If we dont have data for the current number, go get the previous number max date.
var _Val2a = CALCULATE( MAX(Table1[Item]), FILTER(ALLSELECTED(Table1), Table1[Item] < _sel && Table1[Condition] = FALSE() && NOT ISBLANK(Table1[Date]) ))
var _Val2 = CALCULATE( MAX(Table1[Date]), FILTER(ALLSELECTED(Table1), Table1[Item] = _Val2a && Table1[Condition] = FALSE()))

RETURN  COALESCE( _Val1, _Val2)

     

    Output