Count # of Entries, In Order, Based on Another Column

[boykin188]

Hello,

I have a table that has ID numbers combined with Year and Month - 99.9% of the time, this should be a completely unique entry and should not have any duplicates.

So a person with an ID of 200 and a participation date of December 2018 would have an ID: 200201812. A person with an ID of 4356 and multiple participation dates in consecutive months would have an ID of:

4356201810

4356201811

4356201812

4356201901

I'm looking to add a count in a column associated with the ID, i.e.:

4356201810         1

4356201811        2

4356201812        3

4356201901        4

4899201803        1

4899201804         2

77201705             1

77201706             2

The only requirement is that the first appearence of the ID timewise -- also the lowest numerical value, would be count #1, followed by count#2 if it exists and so on.

Thanks for any help!

[Ashish_Mathur]

Hi,

This M code works fine

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "ID", "ID - Copy"),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"ID - Copy", type text}}, "en-IN"), "ID - Copy", Splitter.SplitTextByPositions({0, 6}, true), {"ID - Copy.1", "ID - Copy.2"}),
    Partition = Table.Group(#"Split Column by Position", {"ID - Copy.1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Index"}, {"ID", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"ID - Copy.1"})
in
    #"Removed Columns"

[Anonymous · Accepted solution]

@boykin188 -

You could create 2 calculated columns to separate out the data and a third to get the instance:

User = INT(DIVIDE(Ordering[ID],1000000))
MonthKey = Ordering[ID] - (Ordering[User] * 1000000)
Instance = COUNTROWS(FILTER(ALL(Ordering), AND(Ordering[User] = EARLIER(Ordering[User]), Ordering[MonthKey] <= EARLIER(Ordering[MonthKey]))))

[boykin188]

@Anonymous I think I see what you're trying to do! When I try to do the Instance column though, I'm getting an "EARLIER/EARLIEST refers to an earlier row context which doesn't exist"

INSTANCE = COUNTROWS(FILTER(ALL(Table_name),

AND(Table_name[USER] = EARLIER(Table_name[USER]),

Table_name[MonthKey] <= EARLIER(Table_name[MonthKey]))))

[Anonymous]

@boykin188 - Are you creating a Calculated Column or a Measure?

[boykin188]

Calculated column -- but I also tried putting the DAX into a new measure, and same result. Feel like I'm missing something.

[Anonymous]

@boykin188  - 

It should work, if you create all 3 calculated columns in the same table.

When creating a calculated column, the row context is the row of the table being populated.

FILTER is an iterator, so it checks each row.

EARLIER refers to the row being populated in the table, while the column reference without the "EARLIER" refers to the row within the FILTER iteration.

So I don't know why it's giving you that error. Could you post your pbix?

[boykin188]

I made a small mistake - your DAX was spot on! Thanks everyone 🙂