Help with DAX Allexcept filtering

[nadmem]

Hi,
I have a Table called Tracking, as shown below:

Category,Item Id,Work Date
A,1,1/1/2020
B,1,2/1/2020
C,1,3/1/2020
D,1,4/1/2020
A,2,7/1/2020
B,2,8/1/2020
C,2,9/1/2020
D,2,10/1/2020

Using below measure, I can get Maximum Work Date from above Table.

maxWorkDate =
CALCULATE(
MAX('Tracking'[Work Date]),
ALLEXCEPT('Tracking','Tracking'[Item Id])
)
For Item 1, the maximum work date is 4/1/2020 and for Item 2, the maximum work date is 10/1/2020.

I want to get maximum work date by Excluding Category D. So, For Item 1, the maximum work date should be 3/1/2020 and for Item 2, the maximum work date should be 9/1/2020.

I would like to exclude category D within the measure or I can also exclude it in Visual Filters. Please advise. Thanks

[daxer-almighty]

[Max Work Date] =
calculate(
    max( 'tracking'[work date] ),
    distinct ( 'tracking'[item id] ),
    'tracking'[category] <> "D",
    all ( 'tracking' )
)

[PaulPalma]

@nadmem 

It would depend on what is your requirements.

Do you want to be able filter on visual category and/or itemid to get the max date?

Adding the visual filter or slicer on canvas will work along with a change in your DAX to

CALCULATE(

    MAX('Tracking'[Work Date])

)

or if you have additional columns

CALCULATE(
       MAX('Tracking'[Work Date]),
      ALLEXCEPT('Tracking','Tracking'[Item Id],'Tracking'[Category])
)

Is the requirement to always ignore the Category D?

CALCULATE(

     MAX('Tracking'[Work Date])

     , Tracking[Category] <> "D"

)

again you can add in the line "ALLEXCEPT('Tracking','Tracking'[Item Id],'Tracking'[Category])" if you need to ignore filters on other columns.

Thanks

Paul

[nadmem]

Hi Paul,

Its not working. I had tried adding the category column to ALLEXCEPT before but it changes the behavior completely. Instead of max work date from entire category it just simply repeats the work date. I can send you the file or you can try it yourself. My requirement is to simply unselect category D from visual filter and then have the measure return max date from remainin categories i.e. after excluding Category D via visual filter, for Item 1, the maximum work date should be 3/1/2020 and for Item 2, the maximum work date should be 9/1/2020.

[PaulPalma]

Ok, Sounds like you want to ignore all Category filters.

CALCULATE(

MAX('Tracking'[Work Date])

,ALLEXCEPT('Tracking','Tracking'[Item Id])

, Tracking[Category] <> "D"

)

[nadmem]

Tried that. It didn't work

[nadmem]

[PaulPalma · Accepted solution]

Your DAX expression was not written properly. ")" is not place right. It should be after the Item ID column.

You can review 

CALCULATE - DAX Guide

ALLEXCEPT - DAX Guide

[nadmem]

Yes, you are right. I fixed it and it worked.

[nadmem]

[lbendlin]

Change your measure to 

maxWorkDate = MAXX(Tracking,Tracking[Work Date])

and use a visuals filter to exclude D

[nadmem]

That didn't work. It brought maximum work date from the ENTIRE table i.e. 10/1/2020 whereas after excluding Category D via visual filter, for Item 1, the maximum work date should be 3/1/2020 and for Item 2, the maximum work date should be 9/1/2020.