Add custom column to identify if date and Id are unique

[A_B_M]

Hi,

I have a large dataset of product sales for the last 5 years that creates a new row if the customer bought more than one product in the sale. I want to be able to add a custom column (Orders) that will place a 1 next to the first row with a matching Order Date and Customer ID and then a null for any repeat rows for that order. 

For example

Order Date	Customer ID	Orders
2/2/2024	121212	1
2/2/2024	121212	null
2/2/2024	123456	1
3/2/2024	444444	1
3/2/2024	343434	1
3/2/2024	121212	1
3/2/2024	444444	nulll

What is the easiest way to do this? 

Thanks for your help,

A

[ryan_mayu · Accepted solution]

@A_B_M 

maybe you can try this

1. create an index column in PQ

2. use DAX to create a column

Column = if(ISBLANK(maxx(FILTER('Table','Table'[Order Date]=EARLIER('Table'[Order Date])&&'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Customer ID])),1)

or

Column = if(maxx(FILTER('Table','Table'[Order Date]=EARLIER('Table'[Order Date])&&'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Customer ID]))="",1)