DAX equivalent of GROUP BY and MIN - How do I count products launched, by year?

[AhsenMajid]

I want to create a measure that counts how many products were sold for the first time in a given year. That means if the product was sold in any year prior, it should not count in the current year.

I would expect to do something like this: (code can be run and iterated here: [https://dax.do/fDTAmTl3kDIMaF/][1] )

DEFINE MEASURE 'Sales'[m1] =
    CALCULATE (
        DISTINCTCOUNT (Sales[ProductKey]),
        SUMMARIZECOLUMNS (
                Sales[ProductKey],
                CALCULATETABLE ( Sales,
                        ALL( Sales[Order Date] ),
                        ALL( 'Date'[Date] ),
                        ALL( 'Date'[Calendar Year] )                        
                ),  
                "earliest_sale", MIN( Sales[Order Date] )
        ),
        ALL( Sales[Order Date] ),
        ALL( 'Date'[Date] )
    )

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "number of products sold for the first time", 'Sales'[m1]
)

However, this returns the following:

Which is exactly the same result as the one I get from

EVALUATE

SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    'Sales',
    "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ),
    "total sales", COUNTROWS ( 'Sales' )
)

Finally, going perhaps, a little bit crazy, I tried this:

EVALUATE

SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    FILTER( 'Sales',
            Sales[Order Date] = CALCULATE(  MIN( Sales[Order Date] ),
                                            SUMMARIZE ( CALCULATETABLE ( 'Sales',
                                                                          ALL ( Sales[Delivery Date] ),
                                                                          ALL ( 'Date'[Date] ),
                                                                          ALL ( 'Date'[Calendar Year] )
                                                         ),
                                                         Sales[ProductKey]
                                            ),
                                            ALL ( Sales[Delivery Date] ),
                                            ALL ( 'Date'[Date] ),
                                            ALL ( 'Date'[Calendar Year] )
                                )
    ),
    "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ),
    "sales", COUNTROWS ( 'Sales' )
)

And got:

Any help would be much appreciated. I wan to count the number of products in each year that were never sold before then, i.e. were sold in that year, for the first time ever.

[1]: https://dax.do/fDTAmTl3kDIMaF/

[AlexisOlson · Accepted solution]

Try this:

DEFINE
    MEASURE Sales[m1] =
        VAR __CurrentProducts = DISTINCT ( Sales[ProductKey] )
        VAR __MinDate = MIN ( 'Date'[Date] )
        VAR __FirstSales =
            CALCULATETABLE (
                SUMMARIZE (
                    Sales,
                    Sales[ProductKey],
                    "@FirstSale", MIN ( Sales[Order Date] )
                ),
                REMOVEFILTERS ( 'Date' ),
                __CurrentProducts
            )
        VAR __Result =
            COUNTROWS (
                FILTER (
                    __FirstSales,
                    [@FirstSale] >= __MinDate
                )
            )
        RETURN
            __Result

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "number of products sold for the first time", [m1]
)

Edit: Slightly simpler version of the measure:

VAR __CurrentProducts = DISTINCT ( Sales[ProductKey] )
VAR __MinDate = MIN ( 'Date'[Date] )
VAR __FirstSales =
    ADDCOLUMNS (
        __CurrentProducts,
        "@FirstSale", CALCULATE ( MIN ( Sales[Order Date] ), ALL ( 'Date' ) )
    )
VAR __FirstSalesCurrYear = FILTER ( __FirstSales, [@FirstSale] >= __MinDate )
VAR __Result = COUNTROWS ( __FirstSalesCurrYear )
RETURN
    __Result

[AhsenMajid]

Thanks, @AlexisOlson!

It definitely does something - the totals fall, which is promising. 

But the total are now less than what this gives:

EVALUATE 
{
DISTINCTCOUNT ( Sales[ProductKey] )
}

I'm typing on my mobile, so I'll have a closer look once I'm home. 

But any idea why the totals don't add up to the distinct count of ProductKey?

[AlexisOlson]

Sorry. I put the date inequality the wrong way, but I've edited it above.

It should add up:

https://dax.do/HJJGg9kySHKgRV/

[AhsenMajid]

@AlexisOlson this is absolutely fantastic, thank you!

Before I actually use the measure in a visual, I'm trying to understand how it works. It seems the highlighted part of the code ehre is quite critical:

When I remove this, I get the same result as previously, i.e. the totals exceed the number of unique product keys. 

However, I struggle to understand why that is. To my mind, __FirstSales is already a table that contains only those rows that contain the order date that is the minimum for the product key.

While I realise DAX doesn't create objects when we create table variables, my understanding was that these table variables acted similarly to how vector filtering works in R - i.e. they filtered an existing table based on whether the rows corresponded to the row number in the filter vector. (i.e., if a table had only three rows, and the minimum date was the row, then filtering that table by another table that used sumamrize and kept just the row for which date was min, i.e., a filter vector (0,0,1).

However, based on how you have use this here, it seems a table variable, when used as a filter in another function, does not actally filter the subject table at all. Instead, it just returns all rows, but with the additional columns, caluclated, window function style for the set of filters applied in the variable definition. 

This then allows you apply further filter conditions - essentiall, the line before the line I highlighted literall just creates a new "window function" column, with the window defined by the filter situation in the variable definition. The highlighted line then does the actual filtering. 

Is this new understanding correct?

If so, is this behaviour consistent? Because I could almost swear I have succefully used table variables to filter tables in the past!

And if you have managed to read this far, thank you so, so much for your effort and your help!!!

[AlexisOlson]

Let's go through it line by line.

VAR __CurrentProducts = DISTINCT ( Sales[ProductKey] )

This is the set of distinct product keys in the current filter context. That is, only the products that have rows in the Sales table that correspond to the calendar year context.

VAR __MinDate = MIN ( 'Date'[Date] )

This is the first date in the current filter context. It should be January 1 of the calendar year.

VAR __FirstSales =
    CALCULATETABLE (
        SUMMARIZE (
            Sales,
            Sales[ProductKey],
            "@FirstSale", MIN ( Sales[Order Date] )
        ),
        REMOVEFILTERS ( 'Date' ),
        __CurrentProducts
    )

This is the trickiest part. It's calculating the first sale for each of the current products.

CALCULATETABLE is doing two things to the filter context.

1. It removes any filtering coming from the 'Date' table. In particular, the Calendary Year context is removed.
2. It limits the products to only the current products, as defined previously. Without this filter, we'd get products from all calendar years since the date filtering is removed.

I could/should have written it like this instead. This gives the same result and might be easier to understand:

VAR __FirstSales =
ADDCOLUMNS (
    __CurrentProducts,
    "@FirstSale",
    	CALCULATE ( MIN ( Sales[Order Date] ), REMOVEFILTERS ( 'Date' ) )
)

This is a virual table. It's not part of the model and only exists during the calculation of the measure, just like any other variable inside a measure. Note that the MIN here calculates the minimal order date for each particular product, which is not (usually) the same as the minimal date in the calendar year.

Now that we have all the current products and their first sale date, we filter down the list to those with a first sale date within the current calendar year.

FILTER (
    __FirstSales,
    [@FirstSale] >= __MinDate
)

The FILTER function takes a table as the first argument and a boolean condition as the second argument. It iterates through each row of the table and evaluates the condition for each row. The output is a table, just like the input, but where only the rows where the condition was true.

In our measure, this means that we go through each row in the __FirstSales table and check if the [@FirstSale] date is on or after __MinDate (January 1st of the calendar year).

All that remains is to count the rows of that filtered table and return that count.

[Greg_Deckler]

@AhsenMajid Seems like it should be basically the same as this: 

Better Sales from New Customers - Microsoft Fabric Community

[AhsenMajid]

Thanks @Greg_Deckler! I haven't been able to get it to work unfortunately, what is [Value] in the end? And how would I get it to return a tbale without using Summarize or SummarieColumns around the SUMX (or COUNTX)?

I've got some code here you can edit:
https://dax.do/HJJGg9kySHKgRV/dRZPV7L/