Slicer and ALLEXEPT

[Anonymous]

Hi!

This measure runs perfectly until I use a slicer with values from another table - 'Departments' . How do I write the dax so that the measure not ignore the slicer? 

Income per client = 
            CALCULATE(
                SUMX('Table';
                [Income]);
                     ALLEXCEPT('Table';
                     'Table'[Clientnumber]);
                         FILTER(
                             ALLSELECTED('Calendar'[Date]);
                                 'Calendar'[Date]<=MAX('Calendar'[Date]) && MAX('Calendar'[Date]) <= TODAY()) )

[OwenAuger · Accepted solution]

@Anonymous 

Thankfully, you can provide ALLEXCEPT with a mixture of tables and columns for which to retain filters.

So you can write a measure like this to retain filters on any columns of the Departments table:

Income per client =
CALCULATE (
    SUMX ( 'Table'; [Income] );
    ALLEXCEPT ( 'Table'; 'Table'[Clientnumber]; Departments );
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] );
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && MAX ( 'Calendar'[Date] ) <= TODAY ()
    )
)

Regards,

Owen