DAX - Reference Columns in a Virtual Table

user_14063676 0
Viewed 2083

How can I reference the columns of a table stored in a DAX variable? Including my code below- thank you!

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.
dax powerbi-desktop
1 Answers

Assuming that your data looks like this:

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 |
+------------+---------+------------+

I assumed you want to calculate new customers. I have created a measure that solves the issue using RANKX. From the pair of values CustomerID and Order Date, the calculation takes the first date for each CustomerID. In other words, and using SQL nomenclature, RANKX is partition by CUSTOMERID and OrderBy Order Date. The measure would create a table on the fly, adding a column to rank each CustomerID and Order Date pair. The rank would count the number of orders for each customer. The second step uses DISTINCTCOUNT for CustomerID when the rank created on the table is equal to 1.

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 ) )
user_56210550
Related