powerbi - DAX - 如何按组汇总最新值?
问题描述
我有 2 个表,第一个是数据的净化版本,第二个是我需要计算的。
AnimalCount
Animal DATE Count
Lion 2019-01-01 2
Cat 2019-01-01 45
Cat 2019-01-02 40
Fish 2019-01-02 900
Cat 2019-01-03 50
Fish 2019-01-05 800
Fish 2019-01-06 1200
Cat 2019-01-07 45
Lion 2019-01-07 1
Lion 2019-01-08 0
Calculated
DATE (unique) Sum of latest Count by Animal
2019-01-01 47 = 45 Cats + 2 Lions
2019-01-02 942 = 900 Fish + 40 Cats + 2 Lions
2019-01-03 952 = 50 Cats + 900 Fish + 2 Lions
2019-01-05 852 = 800 Fish + 50 Cats + 2 Lions
2019-01-06 1252 = 1200 Fish + 50 Cats + 2 Lions
2019-01-07 1246 = 1 Lion + 45 Cats + 1200 Fish
2019-01-08 1245 = 45 Cats + 1200 Fish
这是我迄今为止尝试过的查询,它们都给出了与我正在寻找的结果略有不同的结果。
我会非常感谢任何建议?
Query 1.
LatestTotal = SUMX(
GROUPBY(
FILTER(
AnimalCount,
[DATE] <= EARLIER([DATE])
),
[Animal],
"LatestGroupTotal",
SUMX(CURRENTGROUP(), [Count])
),
[LatestGroupTotal]
)
Query 2.
LatestTotal = SUMX(
ADDCOLUMNS(
ADDCOLUMNS(
VALUES(AnimalCount[Animal]),
"AnimalName",
[Animal],
"LastDate",
CALCULATE(
MAX(AnimalCount[DATE]),
NOT(ISBLANK(AnimalCount[Count]))
)
),
"LastValue",
SUMX(
FILTER(
AnimalCount,
[AnimalName] = [Animal]
&& [DATE] = [LastDate]
),
[Count]
)
),
[LastValue]
)
解决方案
首先,为动物总数创建一个度量(为了进一步方便):
Total Animal Count = SUM(AnimalCount[Count])
然后,添加此度量:
Latest Total =
VAR
Current_Date = MAX ( AnimalCount[DATE] )
VAR
All_Latest_Dates =
ADDCOLUMNS (
ALL ( AnimalCount[Animal] ),
"Last Date", CALCULATE (
MAX ( AnimalCount[DATE] ),
AnimalCount[DATE] <= Current_Date )
)
VAR Animal_Latest_Dates =
TREATAS ( All_Latest_Dates, AnimalCount[Animal], AnimalCount[DATE] )
RETURN
CALCULATE ( [Total Animal Count], Animal_Latest_Dates )
结果:
解释:
此公式需要了解 DAX 中的“数据沿袭”概念:
简而言之:
- 将当前上下文中可见的日期存储在变量中;
- 然后,生成一个虚拟表(“All_Latest_Dates”),其中包含所有动物,以及每个“当前”日期的最后一个已知日期;
- 接下来,使用 TREATAS 将此虚拟表应用于 AnimalCount 表(请阅读文章以了解其工作原理);
- 最后,使用虚拟表作为新的过滤上下文来计算动物数量。
这种方法会产生最佳的查询执行计划,即使您的数据很大,该公式也应该为您提供良好的性能。
推荐阅读
- node.js - 使用 DynamoDB 批量更新插入
- java - JAXB 元素
初始化 - Java Spring Boot 2 - docker - 将 IAM 角色传递给 Terraform 的 Docker 容器
- c++ - 找不到 Boost(缺少:序列化)– cmake 错误
- javascript - 在子字符串中换行(JavaScript)
- amazon-web-services - 用于过滤的扫描与查询的 DynamoDB 成本(使用 GSI 哪个分区键与整个表相同)用于过滤
- shell - AIX 中的 cut 命令补充不起作用
- ios - Swift - 使用可解码将嵌套对象转换为对象数组
- fortran - 如何在 GDB 中打印可分配的 fortran int64 数组
- python - 如何编写 python 代码以通过 bash 脚本获取用户的输入