cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Identify Sample Orders that led to a Purchase

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.

All help is appreciated.

Thank You

1 ACCEPTED SOLUTION
Super User

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
)
)
)``````

Super User

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
)
)
)``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors