cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99 Member
Member

How to add Row_number over partition by Customer - DAX or M query

Simple Table like below.

 

ID |    DATE        | Customer | RowNumber (desired)

1     10/09/18       AAAA             1

2     11/09/18       AAAA             2

3     12/09/18       AAAA             3

4     12/09/18       BBBB              1

5     12/09/18       BBBB              1

6     13/09/18       BBBB              2

7     17/09/18       DDDD            1

 

I need a DAX or Power M query calculated column that adds the rownumber and partitions by the customer and date (see desired column above as to what is required)

 

I can do this quite simple in SQL but not sure how to do so in DAX or M query.

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Gravanita Regular Visitor
Regular Visitor

Re: How to add Row_number over partition by Customer - DAX or M query

Ended up trying it my self Smiley Happy

 

table.JPGCreated this table named table3 in Query Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Grouped Rows2" = Table.Group(Source , {"Customer", "DATE"}, {{"Count", each _, type table}}),
    #"Grouped Rows3" = Table.Group(#"Grouped Rows2", {"Customer"}, {{"Count.1", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows3", "Custom", each Table.AddIndexColumn([Count.1],"Id1",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count.1"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Customer", "DATE", "Count", "Id1"}, {"Customer.1", "DATE", "Count", "Id1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom1",{"Count", "Id1"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"ID", "DATE", "Customer", "RowNumber (desired)"}, {"ID", "DATE", "Customer", "RowNumber (desired)"})
in
    #"Expanded Count"

The result is:

Result.JPG

Last column matches the desired result.

5 REPLIES 5
Nick_M New Contributor
New Contributor

Re: How to add Row_number over partition by Customer - DAX or M query

Need to either use EARLIER or define some variables before you start the calculation.  I went the variable route, as just makes more sense to me.  This is a calculated column, not measure

 

Row Number (Desired) = 
VAR CurrentCustomer= Table2[Customer]
VAR CurrentDate = Table2[Date]
RETURN

CALCULATE(
    DISTINCTCOUNT(Table2[Date]),
        FILTER(
            ALL( Table2),
            Table2[Customer]=CurrentCustomer
            && Table2[Date] <= CurrentDate
        )
    )

Index.png

rax99 Member
Member

Re: How to add Row_number over partition by Customer - DAX or M query

Thanks, the logic appears to be sound.

 

Only issue Im having is when scaling to the table size I have. I have over 300K of rows and its taking forever (over an hour and still spinning) - Is there a more efficient way of acheiving the same result? maybe via a Power M query?

Nick_M New Contributor
New Contributor

Re: How to add Row_number over partition by Customer - DAX or M query

FILTER is an iterator, so will take longer.  Being a calculated column it only calculates on refresh, which may may not be ok.

 

I'm sure there is a way to accomplish this in PQ, but still learning M, so a little out of expertise at this point.  

Gravanita Regular Visitor
Regular Visitor

Re: How to add Row_number over partition by Customer - DAX or M query

Gravanita Regular Visitor
Regular Visitor

Re: How to add Row_number over partition by Customer - DAX or M query

Ended up trying it my self Smiley Happy

 

table.JPGCreated this table named table3 in Query Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Grouped Rows2" = Table.Group(Source , {"Customer", "DATE"}, {{"Count", each _, type table}}),
    #"Grouped Rows3" = Table.Group(#"Grouped Rows2", {"Customer"}, {{"Count.1", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows3", "Custom", each Table.AddIndexColumn([Count.1],"Id1",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count.1"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Customer", "DATE", "Count", "Id1"}, {"Customer.1", "DATE", "Count", "Id1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom1",{"Count", "Id1"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"ID", "DATE", "Customer", "RowNumber (desired)"}, {"ID", "DATE", "Customer", "RowNumber (desired)"})
in
    #"Expanded Count"

The result is:

Result.JPG

Last column matches the desired result.