Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Slicer and ALLEXEPT

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

 

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    7 years ago

    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