Forum Discussion

bradc's avatar
bradc
Frequent Visitor
1 year ago
Solved

Filter on multiple table columns

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 da...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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:

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

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
    )