cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Morginador
Frequent Visitor

How to Apply External Filters on ALLEXCEPT()

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.  

 

ALLEXCEPT().PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @Morginador ,

 

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.

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @Morginador ,

 

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors