REMOVEFILTERS or ALLEXCEPT

[PowerRon]

Hi

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

[eliasayyy]

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]))

[PowerRon]

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?
 

[eliasayyy · Accepted solution]

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])

[PowerRon]

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

[eliasayyy]

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

[PowerRon]

Ah, ok

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

[eliasayyy]

yes