Forum Discussion

Acarbalacar's avatar
Acarbalacar
Frequent Visitor
8 years ago
Solved

ALLEXCEPT in a multitable setup

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

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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