Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
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 NumberItem NumberCustomerOrder DateOrder PriceIs Sample?Purchase on Sample?
110A01/01/20230YesYes
212A02/01/20230YesNo
310A03/01/202350NoNo
415B04/01/2023400NoNo
514B05/01/20230YesNo
612B06/01/2023350NoNo

 

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 
The two calculated columns can be:

1.png

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

 

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @Anonymous 
The two calculated columns can be:

1.png

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors