Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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:
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |