问题描述
我有一张桌子，我们称之为 Products 列:
I have a table, let's call it Products with columns:
IDProductId版本- 其他一些栏目…
Id 列是主键，ProductId 对行进行分组.现在我想查看 ProductId 的不同值，其中 Version 最高.
Id column is the primary key, and ProductId groups rows. Now I want to view distinct values of ProductId where Version is highest.
即来自数据集:标识 |产品编号 |版本 |...100 |1 |0 |...101 |2 |0 |...102 |2 |1 |...103 |2 |2 |...
I.e. From data set:Id | ProductId | Version | ...100 | 1 | 0 | ...101 | 2 | 0 | ...102 | 2 | 1 | ...103 | 2 | 2 | ...
我需要得到:标识 |产品编号 |版本 |...100 |1 |0 |...103 |2 |2 |...
I need to get:Id | ProductId | Version | ...100 | 1 | 0 | ...103 | 2 | 2 | ...
在 SQL 中我会写:
In SQL I would write:
SELECT Id, ProductId, Version, OtherColumns
FROM Products p1
WHERE NOT EXISTS
(SELECT 1
FROM Products p2
WHERE p2.ProductId = p1.ProductId
AND p2.Version > p1.Version)
但我不知道如何在 DAX 中表达这一点.这种带有子查询的方法在 PowerBI 中是否不适用?
But I have no idea how to express this in DAX. Is this approach with subqueries inapplicable in PowerBI?
推荐答案
另一种做法是先构造一个product_ids及其最新版本的虚拟表，然后用这个表对原表进行过滤:
Another approach is to first construct a virtual table of product_ids and their latest versions, and then use this table to filter the original table:
EVALUATE
VAR Latest_Product_Versions =
ADDCOLUMNS(
VALUES('Product'[Product_Id]),
"Latest Version", CALCULATE(MAX('Product'[Version])))
RETURN
CALCULATETABLE(
'Product',
TREATAS(Latest_Product_Versions, 'Product'[Product_Id], 'Product'[Version]))
结果:
这种方法的好处是优化查询执行计划.
The benefit of this approach is optimal query execution plan.
这篇关于PowerBI DAX – 同一张表的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！