PowerBI DAX – 同一个表的子查询 [英] PowerBI DAX – subquery to the same table

问题描述

我有一张表，我们称之为带有列的Products:

I have a table, let's call it Products with columns:

• Id
• ProductId
• 版本
• 其他一些列...

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.

即来自数据集:
Id |ProductId |版本 |...
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 | ...

我需要得到:
Id |ProductId |版本 |...
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 – 同一个表的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！