Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to do a sequential numbering by customer

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

1 ACCEPTED 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
OwenAuger
Super User
Super User

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
)

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi @OwenAuger, is that a way to do the numbering in power query M?

It's a calculated column you can create in DAX.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Yes, i know. I would want to do this in power query because this will follow by the filtering process, which i want to filter some unneccessary rows and append it with other datasets.

@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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.