topic Re: Allexcept in DAX Commands and Tips

Allexcept

Cyriackpazhe — Mon, 10 Feb 2025 12:10:57 GMT

Can someone explain how the allexcept used as table expression in summarize work here. Is there any alternative to produce the required meaure

Re: Allexcept

bhanu_gautam — Mon, 10 Feb 2025 13:08:26 GMT

Cyriackpazhe

The ALLEXCEPT function in DAX is used to remove all filters from a table except for the specified columns. In the provided code, ALLEXCEPT is used within the SUMMARIZE function to create a summary table that includes all rows from the Winesales table, but only keeps the filter on the WINE column. This allows the calculation of the average yearly sales for each wine across all years.

Yearly Average Each Wine =
VAR Summarytable =
SUMMARIZE (ALLEXCEPT (Winesales, Wines [WINE]), DateTable [YEAR] )
RETURN
AVERAGEX (Summarytable, [Total Sales])

Re: Allexcept

Cyriackpazhe — Mon, 10 Feb 2025 16:55:21 GMT

Is there any alterantive. Like using Values

Re: Allexcept

Monika_Hr — Tue, 11 Feb 2025 00:58:11 GMT

If you have a Product table with five columns (ProductKey, ProductName, Brand, Flavor, Category), the following syntaxes produce the same result:

ALL ( Product[ProductName], Product[Brand], Product[Flavor] )

ALLEXCEPT ( Product, Product[ProductKey], Product[Category] )

My understanding base on your picture is, that our goal is to get result for average amount of wines sold and the average should be counted per year. I tried to use that approach with my data, where I use chocolate products instead of wines. I created one measure according your photo and another measure using ALL instead of the ALLEXCEPT. Then I controlled the results base on orders in Sales table.