How to Apply External Filters on ALLEXCEPT()

[Anonymous]

Hi!

Can you please help me to understand how to apply external filters?  I am attempting to calculate the total numbers of unbilled hours on associate level [Quantity] based on certain filter criterias as per here below in 'Ärendetransaktioner_Excel' Table.  The total sum is accurate.  

But then I would like to be able to filter total hours per individual and case. However, as shown here in the image here above, the same total is applied across all the rows for all the associates and cases and not distributed.  I have already verified the cardinalty between the tables and relationships between the associate names table and transaction table containing the hours.  

What is I am doint wrong?  Is my syntax incomplete in order to be able to apply external filter? 

Also, why do I get the total number of hours be less (32K vs 37K) when using "ALL" function eventhough the function is supposed to remove ALL filters. 

Your help is great appreciated.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

ALL function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

ALLEXCEPT function removes all context filters in the table except filters that have been applied to the specified columns.

Please try to use ALLSELECTED function, it removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.