Identify Sample Orders that led to a Purchase

[Anonymous]

Hi,
I am trying to find a way to identify if any orders were placed after a customer was sent samples of a product. Orders are categorised as Samples or Purchases.

Samples: Order Price = 0

Purchase: Order Price > 0

What I'm looking for is an order with the same combination of Item Number and Customer, placed after a sample order is placed. If such an order exists then the "Purchase on Sample?" column should indicate "Yes".

This is my sample table.

Order Number	Item Number	Customer	Order Date	Order Price	Is Sample?	Purchase on Sample?
1	10	A	01/01/2023	0	Yes	Yes
2	12	A	02/01/2023	0	Yes	No
3	10	A	03/01/2023	50	No	No
4	15	B	04/01/2023	400	No	No
5	14	B	05/01/2023	0	Yes	No
6	12	B	06/01/2023	350	No	No

Example: For Sample Order [Order Number 1], we have Item and customer combination {10, A} on 01/01/2023. We have another order [Order Number 3] with the same Item-customer combination with Order Price >0. Hence, the "Purchase on Sample?" column for [Order number 1] should be marked as "Yes".

For [Order Numbers 2 & 6], The item number is same but the customer is different. So we cannot say that the Sample order led to a purchase from the customer.

Please help me write the DAX for this. I would prefer using calculated column.

All help is appreciated.

Thank You

[tamerj1 · Accepted solution]

Hi @Anonymous 
The two calculated columns can be:

Is Sample? = 'Table'[Order Price] = 0

Purchase on Sample? = 
IF ( 
    'Table'[Is Sample?],
    NOT ISEMPTY (
        CALCULATETABLE ( 
            'Table',
            ALLEXCEPT ( 'Table', 'Table'[Item Number], 'Table'[Customer] ),
            'Table'[Order Price] > 0
        )
    )
)