Identify how many uniqie items are sent per order

[Andrew_na_lang]

Hi Power BI fam,

I need your help! 
I need to identify the total number of unique items sent by the customer every order.  

 

 
my orders and customer table have a many-to-one relationship through the customer code.
The end result should tell me how many unique items the customer sends every time they send an order. This is identified through the item code we record and reflects on the orders table. 

ANy help is much appreciated

[sturlaws · Accepted solution]

Hi,

I interpret you requirement to be 'number of items in an order which the customer has not bought in a previous order':

number of new products =
VAR _currentDate =
    CALCULATE ( SELECTEDVALUE ( 'Table'[order date] ) )
VAR _currentCustomer =
    CALCULATE ( SELECTEDVALUE ( 'Table'[customer] ) )
VAR _prevProd =
    CALCULATETABLE (
        VALUES ( 'Table'[productID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[order date] < _currentDate
                && 'Table'[customer] = _currentCustomer
        )
    )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Table'[productID] ),
            FILTER (
                'Table',
                'Table'[customer] = _currentCustomer
                    && 'Table'[order date] = _currentDate
                    && NOT 'Table'[productID] IN _prevProd
            )
        )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly