Filter on multiple table columns

[bradc]

Hi All,

I'm trying to create a [FILTER] column in a table that will return TRUE for the minimum [ITEM NUMBER], in an [ITEM GROUP], after the last recorded [FINISH DATE] that is not blank.  The data in the table looks much like this below, and the FILTER column shows the desired result.

I see there are similar problems that have been solved, but I'm really struggling to apply those solutions to my specific need. 

All help is much appreciated.

B

[Bibiano_Geraldo · Accepted solution]

Hi @bradc ,

Create a new calculated column using this DAX code:

FILTER = 
VAR LastFinishDate =
    CALCULATE(
        MAX('Table'[FINISH DATE]),
        ALLEXCEPT('Table', 'Table'[ITEM GROUP])
    )
VAR MinItemNumberAfterDate =
    CALCULATE(
        MIN('Table'[ITEM NUMBER]),
        ALLEXCEPT('Table', 'Table'[ITEM GROUP]),
        'Table'[FINISH DATE] > LastFinishDate || ISBLANK('Table'[FINISH DATE])
    )
RETURN
'Table'[ITEM NUMBER] = MinItemNumberAfterDate

Consider this notes:
In my case, ITEM NUMBER 2 is marked as "TRUE" in the FILTER column because it is considered the last record based on the most recent date in the FINISH DATE column within each ITEM GROUP, as shown below:

[bradc]

Thank you so much for your answer.

Both responses have help me understand filters and the use of variables in DAX a little better.

I have failed to describe adequately just what I was after, but using the solutions provided I think I have come up with the solution I needed.   This solution didn't quite meet my need as it returned the first Item Number without a finish date.  What I actually needed was the first item number AFTER the last recorded finish date.

The variable calculating the Max recorded last finish date for the item was extremely useful however.

This is the solution I ended up applying. 

FILTER_3 =
//VAR LastFinishDate = CALCULATE(MAX('Table'[FINISH DATE]), ALLEXCEPT('Table', 'Table'[ITEM GROUP]))
VAR MaxItemNumberWithDate = CALCULATE(MAX('Table'[ITEM NUMBER]),ALLEXCEPT('Table', 'Table'[ITEM GROUP]),'Table'[FINISH DATE] <> Blank())
RETURN 'Table'[ITEM NUMBER] = MaxItemNumberWithDate +1

Hope others can utilise and learn from the infomration provided here.

Again, thank you so much for your answer.  It is much appreciated.

[Bibiano_Geraldo]

Hi @bradc, happy you find the last way.

This is great solution. 

[Jihwan_Kim]

Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file. It is for creating a new column.

filter CC =
VAR _T =
    FILTER (
        data,
        data[item group] = EARLIER ( data[item group] )
            && data[item number] < EARLIER ( data[item number] )
            && data[finish date] <> BLANK ()
    )
VAR _RecentItemNumberNonBlankFinishDate =
    MAXX ( _T, data[item number] )
VAR _NextItemNumber = _RecentItemNumberNonBlankFinishDate + 1
RETURN
    IF (
        _RecentItemNumberNonBlankFinishDate <> BLANK (),
        data[item number] = _NextItemNumber
    )