时间戳

首页 > 解决方案 > DAX - 虚拟表中的参考列

dax - DAX - 虚拟表中的参考列

问题描述

如何引用存储在 DAX 变量中的表的列？包括我下面的代码 - 谢谢！

measure = 

VAR min_dates = 

//get the min order date for each customer
SUMMARIZECOLUMNS(Orders[Customer ID],"min_date",MIN(Orders[Order Date]))

RETURN

min_dates

// what I want to do here is take the table I created above (min_dates) and do the following: 
// 1) group_by min_date
// 2) count(distinct) Customer ID

// What I'm struggling with is how to reference the columns created in the min_dates table above.

标签： daxpowerbi-desktop

解决方案

假设您的数据如下所示：

Orders
+------------+---------+------------+
| CustomerID | OrderID | Order Date |
+------------+---------+------------+
| 1          | 1       | 25/07/2020 |
+------------+---------+------------+
| 2          | 2       | 26/07/2020 |
+------------+---------+------------+
| 3          | 3       | 27/07/2020 |
+------------+---------+------------+
| 4          | 4       | 28/07/2020 |
+------------+---------+------------+
| 4          | 5       | 29/07/2020 |
+------------+---------+------------+
| 5          | 6       | 30/07/2020 |
+------------+---------+------------+
| 6          | 7       | 31/07/2020 |
+------------+---------+------------+
| 6          | 8       | 01/08/2020 |
+------------+---------+------------+
| 7          | 9       | 01/08/2020 |
+------------+---------+------------+
| 7          | 10      | 03/08/2020 |
+------------+---------+------------+
| 8          | 11      | 03/08/2020 |
+------------+---------+------------+
| 9          | 12      | 04/08/2020 |
+------------+---------+------------+
| 10         | 13      | 05/08/2020 |
+------------+---------+------------+
| 11         | 14      | 05/08/2020 |
+------------+---------+------------+
| 11         | 15      | 06/08/2020 |
+------------+---------+------------+
| 12         | 16      | 06/08/2020 |
+------------+---------+------------+
| 12         | 17      | 06/08/2020 |
+------------+---------+------------+

我假设你想计算新客户。我创建了一个解决问题的措施，使用RANKX. 根据 CustomerID 和 Order Date 这对值，计算采用每个 CustomerID 的第一个日期。换句话说，使用 SQL 命名法，RANKX是按 CUSTOMERID 和 OrderBy Order Date 进行分区。该度量将动态创建一个表，添加一个列来对每个 CustomerID 和 Order Date 对进行排名。排名将计算每个客户的订单数量。DISTINCTCOUNT当在表上创建的排名等于 1 时，第二步使用CustomerID。

UniqueCustomers =
VAR t1 =
    ADDCOLUMNS (
        orders,
        "Rank", RANKX (
            FILTER ( ALL ( Orders ), [CustomerID] = EARLIER ( Orders[CustomerID] ) ),
            [Order Date],
            ,
            ASC,
            DENSE
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Orders[CustomerID] ), FILTER ( t1, [Rank] = 1 ) )

推荐阅读