ALLEXCEPT in a multitable setup

[Acarbalacar]

I have the current formula, to create a new column ranking stations according to their sum of committed cost with the desired filters in ALLEXCEPT.

Rank cost =
RANKX(
ALLSELECTED('Task List'[Station name]);
CALCULATE(SUM('Task List'[Committed Cost]);ALLEXCEPT('Task List';'Task List'[Station name];'Task List'[Asset class];'Task List'[Cost Code name];'Task List'[Logged Year-mon];'Task List'[Station type];'Task List'[Status];'Task List'[Business Unit];'Task List'[Station number])
))

Now, I have rebuild my data connections, meaning most of the variables in allexcept are in different tables, and thus I cant easily adapt my current function. I now the below function does not work, but how could that be fixed? I tried using variables in the dax expression but I could not get it to work.

Rank cost = 
RANKX(
ALLSELECTED('Task List'[Station name]);
CALCULATE(SUM('Task List'[Committed Cost]);ALLEXCEPT('Task List';'Task List'[Station name];'table1'[Asset class];'table2'[Cost Code name];'Table3'[Logged Year-mon];'table4'[Station type];'table5'[Status];'table5'[Business Unit];'table6'[Station number])
))

[Anonymous · Accepted solution]

HI @Acarbalacar,

Nope, current allexcept function seems not support calculate across multiple tables.
In my opinion, I'd like to suggest you use multiple allexcet function with different tables and other filters.

Rank cost =
RANKX (
    ALLSELECTED ( 'Task List'[Station name] );
    CALCULATE (
        SUM ( 'Task List'[Committed Cost] );
        ALLEXCEPT ( 'Task List'; 'Task List'[Station name] );
        ALLEXCEPT ( 'table1'; 'table1'[Asset class] );
        ALLEXCEPT ( 'table2'; 'table2'[Cost Code name] );
        ALLEXCEPT ( 'table3'; 'Table3'[Logged Year-mon] );
        ALLEXCEPT ( 'table4'; 'table4'[Station type] );
        ALLEXCEPT ( 'table5'; 'table5'[Status]; 'table5'[Business Unit] );
        ALLEXCEPT ( 'table6'; 'table6'[Station number] )
    )
)

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Mock data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490