Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How can I get the Numbering column based on the Customer column, renumber when Customer has changed?
Customer Date Amount Numbering
John 1Jan2017 500 1
John 5Feb2017 400 2
John 5Mar2017 600 3
Mary 21Jan2017 200 1
Mary 20Mar2017 600 2
Solved! Go to Solution.
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
To 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 )
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.
Proud to be a Super User!
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
It's a calculated column you can create in DAX.
Proud to be a Super User!
@Anonymous
Here is a rough example of a Powre Query version:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTLUN9Q3MjA0BzJNDQyUYnXgMqb6RjAZE3QZY5iMGVTGN7GoEsg1QjLOCE3KAE1XLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Date", type date}, {"Amount", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Rows", each _, type table}}), #"Sort by Date" = Table.TransformColumns(#"Grouped Rows", {"Rows", each Table.Sort(_,{{"Date", Order.Ascending}}) } ), #"Add Index" = Table.TransformColumns( #"Sort by Date", {"Rows", each Table.AddIndexColumn(_, "Numbering", 1, 1)} ), #"Expanded Rows" = Table.ExpandTableColumn(#"Add Index", "Rows", {"Date", "Amount", "Numbering"}, {"Date", "Amount", "Numbering"}), #"Fix Types" = Table.TransformColumnTypes(#"Expanded Rows",{{"Date", type date}, {"Amount", type number}, {"Numbering", Int64.Type}}) in #"Fix Types"
It groups by Customer, then adds an Index to the nested table then expands.
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |