filter - DAX 聚合和动态过滤器

Question

我正在尝试计算响应切片器选择的聚合数据（按级别 1）（在级别 2）

具体例子：

数据

Level1  Level2  Level3 Data
A         z       q      1
A         zz      q      2
A         zzz     r      3
A         zzzz    r      4
B         z       q      10
B         zz      q      20
B         zzz     r      30
B         zzzz    r      40

按 Level1 聚合数据

选项 1. 使用 GroupBy 创建一个表并使用 Level1 加入它（在关系选项卡中）

Table1_agg =
GROUPBY(
   Table1,
   Table1[Level1],
   "AggL1_Data",sumx(CURRENTGROUP(),Table1[Data])
)

选项 2. 在表 1 中使用过滤器

AggL1_Filter = 
    Calculate(
    sum(Table1[Data]),
    FILTER(
        Table1,
        Table1[Level1]= EARLIER(Table1[Level1])
        )
)  

结果 - 聚合 WORKS

但是，使用 Level2 Slicer 进行过滤不起作用。

聚合数据“AggL1_”仍然显示 10 和 100，我希望看到 5（对于 A）和 50（对于 B）

非常感谢您的帮助，我已经坚持了很长时间，

提前致谢，

杜西奥德

标签： filterfilteringpowerbiaggregationdax

Answer 1

I think you just need to use the ALLSELECTED function.

AggMeasure = CALCULATE(SUM(Table1[Data]), ALLSELECTED(Table1[Level2]))

---

Edit: If you need to only group by Level1 (ignoring the filter context for other possible levels), then you need to make an adjustment.

AggMeasure = CALCULATE(SUM(Table1[Data]),
                 FILTER(ALLSELECTED(Table1),
                     Table1[Level1] IN VALUES(Table1[Level1])))

This uses the ALLSELECTED function as before to preserve the slicer choices but adds a condition that we only consider rows that match the current Level1. For anything in an A row, VALUES(Table1[Level1]) only returns A since that's all it sees in its filter context. (The Total row removes all filter context so returns the sum of the two.)

Instead of IN VALUES(...) you could use = MAX(...) or = MIN(...) or = LASTNONBLANK(...).