DAX: How to do ALLSELECTEDEXCEPT()?

[gazpage]

Title says it all really. I'd like to combine the behaviours of ALLEXCEPT and ALLSELECTED. So remove the filters, other than external filters, from all the columns except a particular one. Is there a function or way to combine these functions?

 

[Ozeroth]

Hi again,

A combination of ALLSELECTED & FILTERS should give you the ALLSELECTEDEXCEPT behaviour:

=
CALCULATE (
    [YourMeasure],
[COLOR=#ff0000][B]    ALLSELECTED ( YourTable ),[/B][/COLOR]
[COLOR=#ff0000][B]    FILTERS ( YourTable[ColumnToKeep] )[/B][/COLOR]
)

The logic is basically use ALLSELECTED on the entire table, then bring back the explicit filters on the column you want to keep.
And we know that FILTERS ( YourTable[ColumnToKeep] ) will be more restrictive than the values of ColumnToKeep that exist in ALLSELECTED ( YourTable ), so we can safely intersect these two filter arguments.

Note that I used FILTERS rather than VALUES. FILTERS returns a column of values explicitly filtered on a column, whereas VALUES would return values that exist in the current context as a result of both explicit filters and cross-filtering.

Does this give you the behaviour you were looking for?

 

[gazpage]

Yep, that does it. Thanks again.

I thought I was starting to have seen, if not be able to use, all the main DAX functions, but FILTERS is new to me. I think I understand your formula to be doing the intersect of the ALLSELECTED and FILTERS, but they are calculated independently before being applied.

I'm sure there are other things I will have struggled with before that FILTERS would have helped with.

 

[caringsharingbristolbilly]

Hi again,

A combination of ALLSELECTED & FILTERS should give you the ALLSELECTEDEXCEPT behaviour:

=
CALCULATE (
    [YourMeasure],
[COLOR=#ff0000][B]    ALLSELECTED ( YourTable ),[/B][/COLOR]
[COLOR=#ff0000][B]    FILTERS ( YourTable[ColumnToKeep] )[/B][/COLOR]
)

The logic is basically use ALLSELECTED on the entire table, then bring back the explicit filters on the column you want to keep.
And we know that FILTERS ( YourTable[ColumnToKeep] ) will be more restrictive than the values of ColumnToKeep that exist in ALLSELECTED ( YourTable ), so we can safely intersect these two filter arguments.

Note that I used FILTERS rather than VALUES. FILTERS returns a column of values explicitly filtered on a column, whereas VALUES would return values that exist in the current context as a result of both explicit filters and cross-filtering.

Does this give you the behaviour you were looking for?

Just wanted to say thanks for the solution, and thanks to the OP for phrasing it in exactly the way I googled it!