Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

REMOVEFILTERS or ALLEXCEPT

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's avatar
    eliasayyy
    3 years ago

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

7 Replies

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

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



    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        eliasayyy
        Icon for Memorable Member rankMemorable Member
        3 years ago

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