Forum Discussion
Filter on multiple table columns
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] = MinItemNumberAfterDateConsider 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:
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 +1Hope others can utilise and learn from the infomration provided here.
Again, thank you so much for your answer. It is much appreciated.
Super User