topic Re: REMOVEFILTERS or ALLEXCEPT in DAX Commands and Tips

REMOVEFILTERS or ALLEXCEPT

Anonymous — Thu, 27 Jul 2023 09:42:37 GMT

see charts below.
The Line should always show the sum of the categories. Also if I only choose one category in the slicer as in the second chart.
I thought this would work:

Sum of Categories Line = CALCULATE([Sum of Categories],ALLEXCEPT(Dates,Dates[Year],Dates[MonthShortName]))

So filter on nothing. Except the year and month used in the visual.
But it doesn't. In the second chart the Line just follows the number of Detection.

Then I created :

Sum of Categories Line1 = CALCULATE([Sum of Categories],REMOVEFILTERS(Categories[Category]))

That one works. But suppose I add another slicer on this page then maybe I have to change the DAX,

Why isn't the ALLEXCEPT working?

Hope someone can help

Regards

Ron

Re: REMOVEFILTERS or ALLEXCEPT

eliasayyy — Thu, 27 Jul 2023 10:04:09 GMT

if you have relatioship between date and table ,
You used in the all except function the calendar or dates table , which removes all filetrs from date which doesnt have your category , so you need to replace Dates with your table that has categories .

Sum of Categories Line = CALCULATE([Sum of Categories],ALLEXCEPT(Table,Dates[Year],Dates[MonthShortName]))

Re: REMOVEFILTERS or ALLEXCEPT

Anonymous — Thu, 27 Jul 2023 11:00:29 GMT

Hi eliasayyy

I don't understand the answer.
I have a Dates table with amongst other Date, Year and MonthShortName.
I have a Category table with, amongst others, Category-Date.
There is a 1:n relation between Dates and Category on the fields Date and Category-Date

What do I have to do to get ALLEXCEPT working?

Re: REMOVEFILTERS or ALLEXCEPT

eliasayyy — Thu, 27 Jul 2023 11:02:55 GMT

ok in your formula , you used allexcept , and they ask you to sepcify a table , you chose dates table ,

so in theory , it will remove all filters inside the dates table only , however if yo uuse all except and choose the category table, it will fremove all filters from categort table

so instead of

allexcept(dates,dates[month])

use allexcept(category table,dates[month])

Re: REMOVEFILTERS or ALLEXCEPT

Anonymous — Thu, 27 Jul 2023 11:29:27 GMT

Hi eliasayyy

you helped me out

This is the solution. Thnx a lot

Sum of Categories Line3 = CALCULATE([Sum of Categories],ALLEXCEPT(Categories, Dates[Year],Dates[MonthShortName]))

Although I not completely undserstand why this doens't work:

Sum of Categories Line2 = CALCULATE([Sum of Categories],ALLEXCEPT(Categories, Categories[Category-Date]))

But I will dive more in reading a clear article of ALLEXCEPT

Re: REMOVEFILTERS or ALLEXCEPT

eliasayyy — Thu, 27 Jul 2023 11:45:53 GMT

on your chart, your slicer is using dates table month as a filter so you need to specify the column the allexcept should respect and keep the filters , in your case its dates table month or dates date. if you want to use categories date in all except , you need to use categories date in your slicer

Re: REMOVEFILTERS or ALLEXCEPT

Anonymous — Thu, 27 Jul 2023 11:48:16 GMT

Ah, ok

with slicer you mean the visual, the line and clustered column chart

Re: REMOVEFILTERS or ALLEXCEPT

eliasayyy — Thu, 27 Jul 2023 11:55:01 GMT

yes