topic All Except is not behaving correctly on PBI in DAX Commands and Tips

All Except is not behaving correctly on PBI

Zaytoon — Mon, 13 Mar 2023 12:17:11 GMT

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.

Re: All Except is not behaving correctly on PBI

andhiii079845 — Mon, 13 Mar 2023 20:16:56 GMT

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/

Re: All Except is not behaving correctly on PBI

Zaytoon — Tue, 14 Mar 2023 08:38:50 GMT

Thanks andhiii079845 actually slicers are from the same table

Re: All Except is not behaving correctly on PBI

andhiii079845 — Tue, 14 Mar 2023 08:43:42 GMT

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.

Re: All Except is not behaving correctly on PBI

tamerj1 — Tue, 14 Mar 2023 10:41:37 GMT

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] ) ) )

Re: All Except is not behaving correctly on PBI

Adamboer — Tue, 14 Mar 2023 12:19:12 GMT

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!