Defining a primary product based on different order numbers on a sales ticket.

[GRH87]

Hi there,

Apologies for the longwinded subject line, it was somewhat hard to condense into something that points directly at my issue, so that's what the text below aims to do!

So what I'm trying to do is relatively simple in theory, but I'm not quite sure how to implement it.

What I'm trying to do:

Column 1 = SalesID (could contain multiple items under the same ID) - thus there are duplicate IDs in this field.

Column 2 = ItemOnOrder (this represents the numerical order of the item on the sales order, under the Sales ID)

e.g. Apples = 0, Oranges = 1, Pineapples = 2.

Column 3 = PrimaryProduct The calculated column I want to implement that returns either a 0 or 1. This will define the 'Primary Product' i.e. 1, this will represent the first product on the sales list. Where 0 = primary, and anything that isn't the first on the order, will be marked as '0' i.e. not primary product.

Problem:

For the most part most the primary product is defined as 0 on the ItemOnOrder, but sometimes the order will start with a 1 and not a 0.

The result in Column 3 (PrimaryProduct) will always be the first number that occurs, either 0 or 1.

If the first number within Column 2 (ItemOnOrder) = 0 then Column 3 (PrimaryProduct) value = 1, all the following numbers will give a Column 3 value of 0.

If no 0 exists in the rows against Column2 (ItemOnOrder), look for the first number (which will only ever be a 1) in Column 2 then Column 3 value will return a 1, all subsequent numbers after 1 will receive a 0 value.

I've been looking at CONTAINS and so forth inside IF statements, but can't get anything solid.

Here is some sample data of the results I would expect to see based on the logic/parameters above.

Would appreciate some expertise here, it will give me a great boost to my DAX learning.

Thanks in advance.

[wdx223_Daniel · Accepted solution]

if(minx(table,if(table[salesid]=earlier(salesid),table[itemonorder],99999))=table[itemonorder],1,0)

[GRH87]

Thank you very much, works a treat. Marked as solution.