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

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

Reply
Zaytoon
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 

MonthCategorySales
Jan-23Books2
Jan-23Pens3
Jan-23Cards3
Feb-23Books2
Feb-23Cards1
Mar-23Books1
Mar-23Cards2

 

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
Adamboer
Responsive Resident
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!

tamerj1
Super User
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] ) )
)
andhiii079845
Super User
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




andhiii079845
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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks andhiii079845 actually slicers are from the same table

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.