cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

All Except is not behaving correctly on PBI

just noticed wrong calcuation by PBI after latest SW version, here is dummy table and DAX expression i tried on excel and it worked perfectly fine and tried on PBI but not giving the correct results.
the measure i created on excel & PBI is :measure 1:=DIVIDE(CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Category],Table1[Month])),CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Month])))

the table

 Month Category Sales Jan-23 Books 2 Jan-23 Pens 3 Jan-23 Cards 3 Feb-23 Books 2 Feb-23 Cards 1 Mar-23 Books 1 Mar-23 Cards 2

i created 2 slicers (month & category) where i expected sales values for selected category in the selected months to be divided by
sales values of all categories in the selected months, however it only works when im clearing the months selection despite the fact that its working 100% accurate in excel.

5 REPLIES 5
Responsive Resident

It looks like there might be an issue with the context being used in your DAX expression. The ALLEXCEPT function is removing filters from all columns except for the specified ones, which may not be what you want in this case.

Instead, you could try using the FILTER function to explicitly filter the table based on the selected month and category, and then calculate the numerator and denominator separately. Here is an example of what the DAX expression could look like:

measure 1 := VAR selected_month = SELECTEDVALUE(Table1[Month]) VAR selected_category = SELECTEDVALUE(Table1[Category]) VAR numerator = CALCULATE( SUM(Table1[Sales]), Table1[Month] = selected_month, LEFT(Table1[Category], 2) = IF(selected_category = "All", LEFT(Table1[Category], 2), LEFT(selected_category, 2)) ) VAR denominator = CALCULATE( SUM(Table1[Sales]), Table1[Month] = selected_month, ALL(Table1[Category]) ) RETURN DIVIDE(numerator, denominator)

This expression uses the LEFT function to extract the first two characters of the Category column, which should match the prefixes used in your original logic. It also checks if the selected category is "All", in which case it includes all categories that have the same prefix as the selected month.

Let me know if this helps or if you have any further questions!

Super User

Hi @Zaytoon

Indeed Power Pivot and Power Bi DAX engines are different and they somethimes behave differently. With Power Bi measure it is recommended to use

``````1 =
DIVIDE (
CALCULATE (
SUM ( Table1[Sales] ),
ALL ( Table1 ),
VALUES ( Table1[Category] ),
VALUES ( Table1[Month] )
),
CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1 ), VALUES ( Table1[Month] ) )
)``````
Super User

Yes than you have the probem. You will filter the table with a slicer before the other calculations, ALL() will not help you in this case. Please create a separate table with DISTINCT values of the columns for the slicer and use this column in your table. You can create a relation between both tables.

Proud to be a Super User!

Super User

Do you create separate tables for the slicer columns or do you use the columns from Table1? It seems that you have this problem:
https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Proud to be a Super User!

Frequent Visitor

Thanks andhiii079845 actually slicers are from the same table