Calculate Filters

[bradc]

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 is marked as false

MaxItemNumberWithDate =
CALCULATE(MAX(Table[Number]), // get the max number
ALLEXCEPT(Table, Table[Item]), // from the item,
Table[Date] <> Blank(),Table[Condition]=False()) // where the date isn't blank AND the Condition is False

I'm just not getting the expected results with the above.

As I said, probably very easy but I just can;t seem to grasp the calculate and filter concepts.

Cheers

b

[bradc · Accepted solution]

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]

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]

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]

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

[sevenhills]

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]

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]

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

[bradc]

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