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

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.

8 Replies

  • 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

  • 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

       

    • 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

         

  • bradc's avatar
    bradc
    Frequent Visitor
    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.