2

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.
Roman
  • 83
  • 2
  • 9

1 Answers1

0

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 ) )
Angelo Canepa
  • 1,701
  • 2
  • 13
  • 21
  • This is great, thank you for taking a look at this. Going through this will be a good learning experience for me - can I ask how you'd do this with my original approach as well? I am still curious around how to reference columns from a DAX "Temp Table". Thanks again. – Roman Aug 07 '20 at 23:19
  • I think your approach is closer to SQL way of thinking rather than DAX. I'm not sure how to replicate your calculation because `DISTINCTCOUNT` doesn't have an equivalent for tables, such as `COUNTX`. – Angelo Canepa Aug 08 '20 at 10:58
  • A follow up - can you help me understand what table this is returning: `FILTER ( ALL ( Orders ), Orders[Customer ID] = EARLIER ( Orders[Customer ID] ) )` I tried to run that piece on its own as a table, but I guess it doesn't have all the context that EARLIER needs to work. I'm trying to learn about EARLIER, seeing that intermediate table would've been useful :( – Roman Aug 11 '20 at 01:30
  • The table returned is used for `RANKX`. The filter is applied to do the partitioning by `CustomerID`. In this example, you can see the partitioning for `RANKX` as well. https://stackoverflow.com/questions/63102302/row-number-partition-by-to-power-bi-dax-query/63106896#63106896 `EARLIER` is used to access to the context of each row, usually is considered an "old" practice in DAX code, but in that case, I thought it was cleaner than the alternative of using variables. – Angelo Canepa Aug 11 '20 at 10:45