Forum Discussion
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
Super UserTo create a calculated column with DAX, here are a couple of options.
Note, I'm assuming the ordering is based on Date, and I'll call the table YourTable.
Numbering =
VAR CurrentRowDate = YourTable[Date]
RETURN
CALCULATE (
COUNTROWS ( YourTable ),
ALLEXCEPT ( YourTable, YourTable[Customer] ),
YourTable[Date] <= CurrentRowDate
)or
Numbering =
RANKX (
CALCULATETABLE ( YourTable, ALLEXCEPT ( YourTable, YourTable[Customer] ) ),
YourTable[Date],
,
ASC
)
- danextian
Hi Anonymous,
This is your post but please allow me to butt in.
Hi OwenAuger,
I am under the impression that CALCULATE() function without using FILTER() cannot be used in a True/False expression. Isn't YourTable[Date] <= CurrentRowDate such an expression? Thus Power BI should have thrown this error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
- OwenAuger
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
Hi OwenAuger, is that a way to do the numbering in power query M?
