Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
nadmem
New Member

Help with DAX Allexcept filtering

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

1 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

View solution in original post

11 REPLIES 11
daxer-almighty
Solution Sage
Solution Sage

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

@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

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.

Ok, Sounds like you want to ignore all Category filters.
CALCULATE(
MAX('Tracking'[Work Date])
,ALLEXCEPT('Tracking','Tracking'[Item Id])
, Tracking[Category] <> "D"
)

Tried that. It didn't work

Capture3.PNG

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

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

 

Capture1.PNGCapture2.PNG

lbendlin
Super User
Super User

Change your measure to 
 
maxWorkDate = MAXX(Tracking,Tracking[Work Date])
 
and use a visuals filter to exclude D

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors