How to make an external slicer work without including it in the ALLEXCEPT function

[Anonymous]

Hello,

As shown in the figure below,I'm ignoring category3's filter in the ALLEXCEPT function, but I want the category3's external slicer to work.

However, in order to apply an external slicer, it is necessary to put category3 in the ALLEXCEPT function.

Then the calculation will not work. I want to know the solution.

thank you

target table

calculation result

measure fomula

not_slicer =
var selectedfilter1 = SELECTEDVALUE('table'[category1])
var table1 = ADDCOLUMNS('table',"sum_value",MAXX(FILTER('table','table'[category1]=selectedfilter1)
,CALCULATE(SUM('table'[value]),ALLEXCEPT('table','table'[category1],'table'[category2]))))
RETURN
MAXX(table1,[sum_value])

add category3

not "E""H" =
var selectedfilter1 = SELECTEDVALUE('table'[category1])
var table1 = ADDCOLUMNS('table',"sum_value",MAXX(FILTER('table','table'[category1]=selectedfilter1)
,CALCULATE(SUM('table'[value]),ALLEXCEPT('table','table'[category1],'table'[category2],'table'[category3]))))
RETURN
MAXX(table1,[sum_value])

[AlexisOlson · Accepted solution]

I think this should work:

MAXX (
    ADDCOLUMNS (
        SUMMARIZE ( 'table', 'table'[category1], 'table'[category2] ),
        "SUM_value", CALCULATE ( SUM ( 'table'[value] ) )
    ),
    [SUM_value]
)

[Anonymous]

@AlexisOlson 

Hello and thank you for answering my question. I have additional questions.

Is it possible to do the same with the ALLEXCEPT function?
I have a reason to use it.

It would be helpful if you could answer this question as well.
Thank you.

[AlexisOlson]

I'm not sure I understand the question. You could put ALLEXCEPT inside the CALCULATE in the DAX I gave but without knowing why you want to use it, I have no way of knowing if it will behave as you intend or not.

[Anonymous]

Hi@AlexisOlson 

thank you for your answer.
I'm sorry for the unclear intention. I asked again what I wanted to do.
I would be very happy if you could answer this question.

https://community.powerbi.com/t5/Desktop/How-to-work-an-external-slicer-without-including-it-in-the/...

thank you

[Anonymous]

Hallo @AlexisOlson 

This is the solution I'm looking for. I am very happy.

Thank you very much!