topic Re: Help with DAX Allexcept filtering in DAX Commands and Tips

Help with DAX Allexcept filtering

nadmem — Thu, 31 Dec 2020 17:09:55 GMT

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

Re: Help with DAX Allexcept filtering

lbendlin — Thu, 31 Dec 2020 17:29:23 GMT

Change your measure to

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

and use a visuals filter to exclude D

Re: Help with DAX Allexcept filtering

PaulPalma — Thu, 31 Dec 2020 17:44:31 GMT

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

Re: Help with DAX Allexcept filtering

nadmem — Thu, 31 Dec 2020 17:49:59 GMT

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.

Re: Help with DAX Allexcept filtering

nadmem — Thu, 31 Dec 2020 19:32:03 GMT

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.

Re: Help with DAX Allexcept filtering

nadmem — Thu, 31 Dec 2020 19:39:42 GMT

Re: Help with DAX Allexcept filtering

PaulPalma — Thu, 31 Dec 2020 20:32:14 GMT

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

CALCULATE(

MAX('Tracking'[Work Date])

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

, Tracking[Category] <> "D"

)

Re: Help with DAX Allexcept filtering

nadmem — Thu, 31 Dec 2020 22:03:32 GMT

Tried that. It didn't work

Re: Help with DAX Allexcept filtering

nadmem — Thu, 31 Dec 2020 22:04:21 GMT

Re: Help with DAX Allexcept filtering

daxer-almighty — Fri, 01 Jan 2021 09:07:53 GMT

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

Re: Help with DAX Allexcept filtering

PaulPalma — Sat, 02 Jan 2021 11:22:21 GMT

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

Re: Help with DAX Allexcept filtering

nadmem — Wed, 06 Jan 2021 17:55:20 GMT

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