Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
chongsm2867
Regular Visitor

ALLEXCEPT Confusion

Hi,

 

I have an issue with ALLEXCEPT. I have a factinternetsales (fact) table with relationship to a few dimension tables (calendar, customer, product, promotion). Slicers were added from calendar (year), customer (Englishoccupation) and promotion (promotionkey) tables, setting up to test the behaviour of ALLEXCEPT and displaying the value through a card visual : 

 

Measure: 

AllExcept = CALCULATE(

    SUM(FactInternetSales[SalesAmount]),

    ALLEXCEPT(FactInternetSales, FactInternetSales[SalesTerritoryKey]))

Reference: Eg_1

ALLEXCEPT.pbix

 

From what I know (i could be wrong), ALLEXCEPT will respect the filters from dimension tables but remove any filters directly applied to the fact table. This means when I select different values from the dimension table slicers, the measure value will change accordingly. But this is not happening, the measure value remain static no matter what values were selected from the slicer. Am confused because I expect the measure value to change but this is not happening! 

 

Next, I have another measure but this time I included a filter function, as follow:

AllExcept with Filter = CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    ALLEXCEPT(FactInternetSales, FactInternetSales[SalesTerritoryKey]),
    FILTER(FactInternetSales, FactInternetSales[SalesAmount] > 100)
)
Reference: Eg_2
 
The opposite happens! The measure values changes in response to the slicer value.
 
So what's wrong with my formulas. Any advice would be appreciated.
 
Thanks!
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Ideally you should not filter fact tables. You filter dimension tables, and the data model does the rest of the work for you.   If you filter fact tables you risk getting wrong results if that filter interferes with a dimension filter.

 

ALLEXCEPT is a very blunt instrument (similar to ALL) and should be used sparingly.  REMOVEFILTERS is much more gentle.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Ideally you should not filter fact tables. You filter dimension tables, and the data model does the rest of the work for you.   If you filter fact tables you risk getting wrong results if that filter interferes with a dimension filter.

 

ALLEXCEPT is a very blunt instrument (similar to ALL) and should be used sparingly.  REMOVEFILTERS is much more gentle.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors