How to remove unwanted rows as a result of ALLEXCEPT function

[jujiro-eb]

I am posting a simplified version of my my model with changed table names, hoping that the content would be clear to explain the issue.

I have the following model:

I have a measure to report crtical sales for a given date and category, regardless of any other filter applied by the user like product name.

CriticalSales = CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(Sales, Category[Name], Dates[As of Date], Sales[critical_flg]))

The measure is returning value as expected.  However, when the measure is used in a Table Visual, I am seeing, what seems to be expanded Sales table, i.e. it has all products, even though they do not belong to the selected category.

I understand the underlying reason.  ALLEXCEPT is removing all filters from the Sales table.  

Currently, I am using a hack to remove unwanted rows by adding a visual filter as SalesAmount <> blank.  Surprisingly, it works, even though I was expecting it fail, due to ALLEXCEPT's behavior.

My question is what is the best practice to remove those unwanted rows or an alternate way to accomplish what I am doing? 

Thanks.

[Anonymous · Accepted solution]

// You have to be very careful with ALLEXCEPT.
// Please read articles about this function
// on www.sqlbi.com to understand its behaviour.
// This function is abused most of the time
// becasue people don't know exactly how
// it works.

// This is probably what you really need
// and if not, then try to play with this
// formula.
CriticalSales =
CALCULATE(
    SUM( Sales[SalesAmount] ),
    SUMMARIZE(
        Sales,
        Category[Name],
        Dates[As of Date]
    ),
    Sales[critical_flg],
    ALL( Sales )
)

[jujiro-eb]

Thank you!  This is exactly what I wanted.  
Calculate critical sales by date/category for all products, regardless of any other filters present on Sales table.  And, you are absolutely right, ALLEXCEPT is very tricky and misunderstood.

[Anonymous]

By the way, your measure and my measure will calculate the sales amount for any product you put into a matrix/table. It's not really clear what you want... If you have some special categories that you want to return values for, then you have to explicitly filter by them. It would be good if you could manually create an example that would explain your needs.

[amitchandak]

@jujiro-eb , Nor very clear. Also, not sure how you were able to use all except across the table

CriticalSales = CALCULATE(SUM(Sales[SalesAmount]), filter(allselected(Category), Category[Name] =max(Category[Name]))
, filter(allselected(Dates), Dates[As of Date] =max(Dates[As of Date]))
, filter(allselected(Sales), Sales[critical_flg] =max(Sales[critical_flg])) )

[Anonymous]

@amitchandak

He was able to use ALLEXCEPT across tables because this is how expanded tables behave. If you see a table name somewhere in code without any column name standing by it, then you're dealing with the expanded table version of the table, and this gives you access to the columns of the expanded table, which is what happens under ALLEXCEPT. Note that the first table under ALLEXCEPT stands there on its own without any column name, so it's expanded.

[jujiro-eb]

Hi Amit,

>> Nor very clear. Also, not sure how you were able to use all except across the table
The documentation is not clear about that, but it works.  I came across accidentally.

I believe the solution you have suggested, will not remove any other filters, if they were in place (like product name.)