DAX ALLEXCEPT to sum by category of multiple dimension tables

Question

I would like to calculate total by category. The category is in the dimension table.

Here is sample file: DAX ALLEXCEPT total by category.pbix

I have the following model:

These are my expected results. Total by Color:

I thought I could achieve expected results by the following measure:

ALLEXCEPT_color =
CALCULATE (
    [Sales],
    ALLEXCEPT (
        FactTable, -- surprisingly 'dim1' table in that place gives wrong results
        dim1[Color]
    )
)

Or alternatively using method suggested by Alberto Ferrari https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/:

ALL_VALUES_color = 
    CALCULATE (
        [Sales],
        ALL (FactTable), -- again, 'dim1' produces wrong results, has to be FactTable
        VALUES ( dim1[Color] )
    )

Both these measures work and return proper results. However they multiply displayed results making Cartesian product of all the dimensions. Why? How to prevent it?

I achieve expected results with measure:

Expected_Results_Color =
IF (
    ISBLANK ( [Sales] ),
    BLANK (),
    [ALLEXCEPT_color]
)

Probably I am missing something about ALLEXCEPT function so I do not get what I want for the first shot. What is the logic behind using ALLEXCEPT function with multiple tables, especially with far off dimensions, away from the center of star schema.

What pattern to use? Here I found promising solution which looks like this:

ByCategories =
CALCULATE (
    SUM ( FactTable[Sales] ),
    ALLEXCEPT (
        dim1,
        dim1[Color]
    ),
    ALLEXCEPT (
        dim2,
        dim2[Size]
    ),
    ALLEXCEPT (
        dim3,
        dim3[Scent]
    )
)

But as I tested it before it does not work. It does not aggregate [Sales] by dimensions but produces [Sales] as they are.

So I found out that this is the correct direction:

ByCategories =
CALCULATE (
    SUM ( FactTable[Sales] ),
    ALLEXCEPT (
        FactTable, -- here be difference
        dim1[Color],
        dim2[Size],
        dim3[Scent]
    )
)

I speculate there might be also another way.

Measure = 
    var MyTableVariable =
    ADDCOLUMNS (
        VALUES ( dim1[color] ),
        "GroupedSales", [Sales]
    )
    RETURN
        ...

If only we could retrieve single scalar value of GroupedSales from MyTableVariable and match it with appropriate color in table visual.

I would be very grateful for any further insights in calculating total for category.

I would recommend to change your data model. Create dim "Color", dim "Size", and have corresponding ids in the fact table. Sharing the same id between dimensions makes no sense. — RADO
@RADO Of course your remark is true. Nevertheless ALLEXCEPT makes crossjoin. I have updated my example and I uploaded new pbix file. — Przemyslaw Remin
that's an improvement; however, you might need to do one more thing. What are these a, b, c, etc ids? what's their meaning? It seems to me that they must be a dimension too (with the corresponding id in the fact table). Then, in the matrix you should use these a, b, c etc from the dimension, not from the fact — RADO
Are there any rules for using multiple tables with ALLEXCEPT? Actually this is the root of my question. How should I make group by's with ALLEXCEPT on multiple dimensions? Should I start from the FactTable? ALLEXCEPT(FactTable, dim1[color], dim2[size]) or should I make CALCULATE( [Sales], ALLEXCEPT(dim1, color), ALLEXCEPT(dim2, size) ) ? — Przemyslaw Remin

Mankarse Mankarse · Accepted Answer · 2019-11-22T09:55:09

This is expected behaviour.

Power BI tables will include every row for which any measure in the table does not evaluate to BLANK().

ALLEXCEPT stops the values in the id and size columns from affecting the filter context when [Sales] is computed, and so every possible value for these two columns will give the same (non-blank) result (this causes the cartesian product that you see).

For example, on the (a, black, big) row, the filter context for the measures contains:

FactTable[id] = {"a"}
dim1[color] = {"black"}
dim2[size] = {"big"}

Then CALCULATE([Sales], ALLEXCEPT(...)) removes the FactTable[id] and dim2[size] from the filter context when evaluating [Sales]; so the new filter context is just:

dim1[color] = {"black"}

[Sales] in this filter context is not BLANK(), so the row is included in the result.

The proper way to fix this is to wrap the result in an IF, as you do in your Expected_Results_Color measure, or to add a filter on [Sales] not Blank to the table in Power BI.

DAX ALLEXCEPT to sum by category of multiple dimension tables

1 Answers