How to do a sequential numbering by customer

Hi danextian

Good question. Here is a pbix file demonstrating that it works.

From Microsoft's documentation, the rules for Boolean filter expressions are:

• The expression cannot reference a measure.

• The expression cannot use a nested CALCULATE function.

• The expression cannot use any function that scans a table or returns a table, including aggregation functions.

So basically you can create any boolean expression involving a single column as long as you don't use a measure/CALCULATE/table-scanning function.

In my case, YourTable[Date] <= CurrentRowDate is a Boolean expression comparing YourTable[Date] to a variable CurrentRowDate (effectively a constant at this point in the code), but CALCULATE isn't involved in this expression. The definition of CurrentRowDate didn't use CALCULATE either.

With the advent of variables, you can use a variable anywhere in a CALCULATE filter argument where a constant would have been allowed. This is one way of getting around the restrictions on Boolean filter arguments listed above. So if my definition for CurrentRowDate had involved CALCULATE, I could have still used CurrentRowDate the same way I did within CALCULATE.

Incidentally, I could have written this calculated column as:

Numbering = 
CALCULATE (
    COUNTROWS ( YourTable ),
    ALLEXCEPT ( YourTable, YourTable[Customer] ),
    YourTable[Date] <= EARLIER ( YourTable[Date] )
)

Regards,

Owen