NEED HELP ALLEXCEPT function

[Anonymous]

hi All,

@amitchandak 

ALLEXCEPT function is not working as expected..Ideally it should retain filter which has been specified in argument and remove all external filters please correct if i need to consider other points as well like relationship model while working with  ALLEXCEPT

Below are the measure which i used

All() Sales = CALCULATE(sum('Order Details'[Sales]),all(Employees),all(Products))

AllExcptProductName = calculate(sum('Order Details'[Sales]),ALLEXCEPT(Products,Products[ProductName]))

here i can see it has sliced the data as per country ,city and product name 

[PaulDBrown]

@Ark1 

The reason the measure is delivering unexpected results is to do with the fields you are using in the visual vs. the table reference in the ALLEXCEPT function.

See the following example Both channel and item come from dimension tables. You can see the rendition of different ALLEXCEPT expressions (NB: 'Sales' is the fact table and Dim Item is a dimension table to the 'Sales' table)

1)

Sales ALLEXCEPT Item = CALCULATE([Sum of Sales], ALLEXCEPT(Sales, Sales[Item]))

2)

Sales ALLEXCEPT Dim Item = CALCULATE([Sum of Sales], ALLEXCEPT('DIM Item', 'DIM Item'[Item]))

3)

Sales Allexcept (DatDim) = CALCULATE([Sum of Sales], ALLEXCEPT(Sales, 'DIM Item'[Item]))

which gets you this:

As you can see, if you are using dimension tables as the filter contexts, you need to write the expression as ALLEXCEPT(FactTable, DimTable[DimField).

If, however, the filter context are fields from the fact table (so not from dimension tables), you need to write ALLEXCEPT(FactTable, FactTable[field)).

So basically you must always refer to the Fact Table as the table you wish to remove the filters from, and  use the field you are using as a filter context in the visual as the column you wish to keep the filters on. Whatever the case, you cannot use the dimension table as the table expression, since the ALLEXCEPT will remove the filters from that same  dimension table (when what you need is to remove all the other filters!). Make sense?

Edit: Another way of getting the results is (again depending on which field is used as a filter context in the visual)

is

Alternative to allexcept (Dim Item) = CALCULATE([Sum of Sales], 
                                FILTER(ALL(Sales), 
                                Sales[Item] = SELECTEDVALUE('DIM Item'[Item])))

Alternative to allexcept (Fact Item) = CALCULATE([Sum of Sales], 
                                FILTER(ALL(Sales), 
                                Sales[Item] = SELECTEDVALUE(Sales[Item])))

[Anonymous]

Hi @PaulDBrown 

I really appreciate your brief explanation ....What if the fact and dimension lies in one table only as in my pbix ....why is DAX so complicated?

Regards,

Husna

[PaulDBrown · Accepted solution]

@Anonymous 

Your example is the same as my first example. The field you are using in the table visual is from a dimension table (Products), so you need to write the expression as:

AllExcptProductName = CALCULATE(SUM('Order Details'[Sales]),ALLEXCEPT('Order Details',Products[ProductName]))

[Ark1]

If I understand correctly, you are trying to calculate the sales per city and per country?

If you want to show the total sales per city you should use this probably: calculate(sum('Order Details'[Sales]),ALLEXCEPT(TABLENAME,TABLENAME[City]))

If you want to show the total sales per city you should use this probably: calculate(sum('Order Details'[Sales]),ALLEXCEPT(TABLENAME,TABLENAME[Country]))

Allexcept means that you are removing all filters, except the one you state in the Allexcept function.

At the moment I am not able to test, so let me know if this was what you where looking for

[Anonymous]

@Ark1  im looking for Total sales per product name , how can i write using allexcept function?

[Ark1]

Could you please share your file? I will have a look

[Anonymous]

@Ark1 @amitchandak you can check my pbix here https://github.com/hwpowerbi/Power-bi-Repository 

[amitchandak]

@Anonymous , Please refer to this -https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

And check if this can help you.

Or

Can you share sample data and sample output in table format? I need to try and check