DAX Grouping Evaluation

Question

In this measure - I'm creating a temp table to group by customer ID and return the year of the min order_date for each customer. I then want to count the # of customers that show up for a given year (basically just a row count).

What I'm struggling to understand is - this formula doesn't seem to look at the SUMMARIZE table within it. If I put year and this measure in a matrix, it counts based on the raw table, not the grouped version built by SUMMARIZE in the formula. Any ideas why it's evaluating this way?

COUNTROWS(
SUMMARIZE(
    Orders,
    Orders[Customer ID],
    "min_order_date_year",
    MIN(Orders[Order Date].[Year])))

user_3652345 · Answer

The formula is OK as per logic you provided to it. You have to clear about both your requirement and what your are writing in your code. Your formula is returning exactly what you are instructing it.

If I understand correct, you simply need the Customer count for the Minimum Year. For say if you have 6 unique customer for Year 2019 and 11 unique customer for Year 2020, you are looking here for value 6 to return by your measure.

Now, what your summarize table actually returning? if you create a separate custom table for the Summarize code only as below, you can see the table will actually hold all your customer name/id in first column and the second column will hole the MIN year available for that customer.

orders_summarize = 
SUMMARIZE(
    Orders,
    Orders[customer id],
    "min_order_date_year",MIN(Orders[Order Date].[Year])
)

So basically you have list of all customer in your summarize table. And now your are counting rows of your summarize table which is actually returning the total number of unique customers.

Finally, if you wants customer count for a specific Year (like MIN year), follow these below steps-

Step-1: Create a custom summarized table as below-

store_summarized_table = 
SUMMARIZE(
    store,
    store[Customer ID],
    "mindate",MIN(store[Order Date])
)

Step-2: create a measure as-

count_cust_id = COUNT('store_summarized_table'[Customer ID])

Step-3: Now configure your Matrix visuals as shown in the below image. You can also get the output in the image-

To avoid the Physical Table, you can do this below-

Step-1: Create a Custom Column as below-

is_min_year = 

// -- keep current row's customer id to a variable
VAR current_cust_id = store[Customer ID]
// -- keep current row's YEAR value to a variable
VAR current_year = store[Order Date].[Year]

// -- find the MIN YEAR from order date for the current row customer id
VAR min_year_current_custommer_id =  
CALCULATE(
    MIN(store[Order Date].[Year]),
    FILTER(
        store,
        store[Customer ID] = current_cust_id
    )
)

// -- check the current row's year is the MIN year of order date for the customer as well or not.
RETURN IF(current_year = min_year_current_custommer_id, 1,0)

OR create a measure as-

is_min_year_measure = 

VAR min_order_year_for_current_customer = 
CALCULATE(
    MIN(store[Order Date].[Year]),
    FILTER(
        ALL(store),
        store[Customer ID] = MIN(store[Customer ID])
    )
)

RETURN
IF ( MIN(store[Order Date].[Year]) = min_order_year_for_current_customer, 1,0)

Step-2: Create a Measure as below-

For created Custom Column

count_cust_for_min_year = 
CALCULATE(
    DISTINCTCOUNT(store[Customer ID]),
    FILTER(
        store,
        store[is_min_year] = 1
    )
)

For Created Measure

count_cust_for_min_year = 
CALCULATE(
    DISTINCTCOUNT(store[Customer ID]),
    FILTER(
        store,
        [is_min_year_measure] = 1
    )
)

Now add "Order Date" and measure "count_cust_for_min_year" to your Matrix. The out put will same as below-

DAX Grouping Evaluation

1 Answers