Forum Discussion
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
Your DAX expression was not written properly. ")" is not place right. It should be after the Item ID column.
You can review
11 Replies
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.
- lbendlin
Super User
Change your measure tomaxWorkDate = MAXX(Tracking,Tracking[Work Date])and use a visuals filter to exclude DThat 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.
- daxer-almighty
Solution Sage
[Max Work Date] = calculate( max( 'tracking'[work date] ), distinct ( 'tracking'[item id] ), 'tracking'[category] <> "D", all ( 'tracking' ) )
