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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.