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

问题描述

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

以下是示例文件：

这些是我的预期结果。按颜色总计：

我以为我可以通过以下措施获得预期结果：

  ALLEXCEPT_color = 
计算（
 [销售]，
 ALLEXCEPT（
 FactTable，-令人惊讶的是，该位置的'dim1'表给出了错误的结果
 dim1 [Color] 
）
）
  

或者使用Alberto Ferrari建议的方法

可能我丢失了关于 ALLEXCEPT 函数的一些知识，所以我没有得到想要的第一张照片。将ALLEXCEPT函数用于多个表，尤其是尺寸远于星形模式中心的表，背后的逻辑是什么。

要使用哪种模式？ 这里我找到了有希望的解决方案看起来像这样：

  ByCategories = 
 CALCULATE（
 SUM（FactTable [Sales]），
 ALLEXCEPT（
 dim1，
 dim1 [Color] 
），
 ALLEXCEPT（
 dim2，
 dim2 [Size] 
）， 
 ALLEXCEPT（
 dim3，
 dim3 [Scent] 
）
）
  

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

所以我发现这是正确的方向：

 按类别= 
计算（
之和（FactTable [Sales]），
 ALLEXCEPT（
 FactTable，-这里是差值
 dim1 [Color]，
 dim2 [Size]，
 dim3 [Scent] 
）
）
  

我推测可能还有另一种方法。

  Measure = 
 var MyTableVariable = 
 ADDCOLUMNS（ 
值（dim1 [color]），
 GroupedSales，[销售] 
）
返回
 ... 
  

如果只有这样，我们可以从MyTableVariable中检索GroupedSales的单个标量值，并将其与表格视觉中的适当颜色匹配。

在计算类别总计时，我将不胜感激。

解决方案

这是预期的行为。

Power BI表将包括表中任何度量均不等于 BLANK的每一行。 （）。

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

例如，在（ a，黑色，大）行，度量的过滤器上下文包含：

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

然后 CALCULATE（[Sales]，ALLEXCEPT（...））删除 FactTable [id] 和 dim2 [size] 评估 [销售] 时的过滤器上下文；因此新的过滤器上下文为：

  dim1 [color] = { black} 
  

[销售] 在此过滤器上下文中不是 BLANK （），因此该行包含在结果中。

解决此问题的正确方法是将结果包装在<$ c中$ c> 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屋！