DAX ALLEXCEPT 按多维表的类别求和 [英] DAX ALLEXCEPT to sum by category of multiple dimension tables

问题描述

我想按类别计算总数.类别在维度表中.

这里是示例文件:

这些是我的预期结果.按颜色总计:

我认为我可以通过以下措施达到预期的效果:

ALLEXCEPT_color =计算 ([销售量]，除外(FactTable，——令人惊讶的是，那个地方的dim1"表给出了错误的结果dim1[颜色]))

或者使用 Alberto Ferrari 建议的方法

可能我缺少关于 ALLEXCEPT 函数的一些内容，所以我没有得到我想要的第一个镜头.对多个表使用 ALLEXCEPT 函数背后的逻辑是什么，尤其是远离星型模式中心的维度.

使用什么模式?这里 我找到了有希望的解决方案看起来像这样:

按类别 =计算 (总和(事实表[销售额])，除外(昏暗1，dim1[颜色]),除外(昏暗2，dim2[尺寸]),除外(昏暗3，dim3[香味]))

但正如我之前测试过的那样，它不起作用.它不会按维度聚合 [Sales]，而是按原样生成 [Sales].

于是我发现这是正确的方向:

按类别 =计算 (总和(事实表[销售额])，除外(FactTable, -- 这里有区别dim1[颜色],dim2[大小],dim3[香味]))

我推测可能还有另一种方式.

测量 =var MyTableVariable =添加列 (值( dim1[color] )，"GroupedSales", [销售])返回...

如果我们能从 MyTableVariable 中检索 GroupedSales 的单个标量值并将其与表格视觉对象中的适当颜色匹配就好了.

如果您在计算类别总数方面有任何进一步的见解，我将不胜感激.

解决方案

这是预期行为.

Power BI 表将包含表中任何度量值未计算为 BLANK() 的每一行.

ALLEXCEPT 阻止 id 和 size 列中的值在 [Sales] 时影响过滤器上下文被计算出来，因此这两列的每个可能值都会给出相同的(非空白)结果(这会导致您看到的笛卡尔积).

例如，在 (a, black, big) 行上，度量的过滤器上下文包含:

FactTable[id] = {"a"}昏暗1 [颜色] = {黑色"}dim2[大小] = {大"}

然后 CALCULATE([Sales], ALLEXCEPT(...)) 将 FactTable[id] 和 dim2[size] 从评估 [Sales] 时的过滤器上下文；所以新的过滤器上下文就是:

dim1[color] = {"black"}

此过滤器上下文中的

[Sales] 不是 BLANK()，因此该行包含在结果中.

解决此问题的正确方法是将结果包装在 IF 中，就像您在 Expected_Results_Color 度量中所做的那样，或者在 [ 上添加过滤器销售] 不是空白 到 Power BI 中的表.

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.

解决方案

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 按多维表的类别求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！